package com.infodms.dms.dao.claim.dealerClaimMng;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.infodms.dms.bean.AclUserBean;
import com.infodms.dms.bean.ClaimListBean;
import com.infodms.dms.bean.LabourPartBean;
import com.infodms.dms.bean.NewPartBean;
import com.infodms.dms.bean.TtAsActivityBean;
import com.infodms.dms.bean.TtAsRepairOrderExtBean;
import com.infodms.dms.bean.TtAsWrApplicationExtBean;
import com.infodms.dms.bean.TtAsWrForeapprovalBean;
import com.infodms.dms.common.Constant;
import com.infodms.dms.common.Utility;
import com.infodms.dms.common.getCompanyId.GetOemcompanyId;
import com.infodms.dms.dao.common.BaseDao;
import com.infodms.dms.po.FsFileuploadPO;
import com.infodms.dms.po.TcCodePO;
import com.infodms.dms.po.TmBusinessChngCodePO;
import com.infodms.dms.po.TmBusinessParaPO;
import com.infodms.dms.po.TmDownParameterPO;
import com.infodms.dms.po.TmPtPartBaseExtPO;
import com.infodms.dms.po.TmPtPartBasePO;
import com.infodms.dms.po.TmPtSupplierPO;
import com.infodms.dms.po.TmVehicleExtPO;
import com.infodms.dms.po.TmVehiclePO;
import com.infodms.dms.po.TmVehiclePinRequestPO;
import com.infodms.dms.po.TrBalanceClaimPO;
import com.infodms.dms.po.TtAsActivityNetitemPO;
import com.infodms.dms.po.TtAsActivityPO;
import com.infodms.dms.po.TtAsActivityPartsPO;
import com.infodms.dms.po.TtAsActivityRepairitemExtPO;
import com.infodms.dms.po.TtAsRepairOrderBackupPO;
import com.infodms.dms.po.TtAsRepairOrderExtPO;
import com.infodms.dms.po.TtAsRepairOrderPO;
import com.infodms.dms.po.TtAsRoAddItemPO;
import com.infodms.dms.po.TtAsRoLabourPO;
import com.infodms.dms.po.TtAsRoManagePO;
import com.infodms.dms.po.TtAsRoRepairPartPO;
import com.infodms.dms.po.TtAsWrAppauthitemPO;
import com.infodms.dms.po.TtAsWrApplicationExtPO;
import com.infodms.dms.po.TtAsWrApplicationPO;
import com.infodms.dms.po.TtAsWrForeapprovalPO;
import com.infodms.dms.po.TtAsWrForeapprovalitemPO;
import com.infodms.dms.po.TtAsWrGamefeePO;
import com.infodms.dms.po.TtAsWrInformationqualityExtPO;
import com.infodms.dms.po.TtAsWrInformationqualityPO;
import com.infodms.dms.po.TtAsWrLabouritemPO;
import com.infodms.dms.po.TtAsWrModelGroupPO;
import com.infodms.dms.po.TtAsWrModelItemPO;
import com.infodms.dms.po.TtAsWrNetitemExtPO;
import com.infodms.dms.po.TtAsWrOtherfeePO;
import com.infodms.dms.po.TtAsWrOutrepairPO;
import com.infodms.dms.po.TtAsWrPartsitemExtPO;
import com.infodms.dms.po.TtAsWrPartsitemPO;
import com.infodms.dms.po.TtAsWrQamaintainPO;
import com.infodms.dms.po.TtAsWrRuleListPO;
import com.infodms.dms.po.TtAsWrWrauthorizationPO;
import com.infodms.dms.po.TtAsWrWrlabinfoExtPO;
import com.infodms.dms.po.TtDealerActualSalesPO;
import com.infodms.dms.po.VwMaterialGroupPO;
import com.infodms.dms.util.CommonUtils;
import com.infodms.dms.util.UserProvinceRelation;
import com.infodms.dms.util.businessUtil.GetVinUtil;
import com.infoservice.mvc.context.ActionContext;
import com.infoservice.po3.POFactory;
import com.infoservice.po3.POFactoryBuilder;
import com.infoservice.po3.bean.PO;
import com.infoservice.po3.bean.PageResult;
import com.infoservice.po3.core.callback.DAOCallback;

/**
 * 
 * @ClassName: ClaimBillMaintainDAO
 * @Description: TODO(经销商索赔单维护DAO)
 * @author wangchao
 * @date Jun 5, 2010 3:37:38 PM
 * 
 */
public class ClaimBillMaintainDAO extends BaseDao {

	public static Logger logger = Logger.getLogger(ClaimBillMaintainDAO.class);
	private static final ClaimBillMaintainDAO dao = new ClaimBillMaintainDAO();

	public static final ClaimBillMaintainDAO getInstance() {
		return dao;
	}

	@Override
	protected PO wrapperPO(ResultSet rs, int idx) {
		// TODO Auto-generated method stub
		return null;
	}

	/**
	 * 
	 * @Title: getWgroupId
	 * @Description: TODO(通过车型ID取得工时对应的车型组ID)
	 * @param
	 * @return 设定文件
	 * @return List<TtAsWrModelItemPO> 返回类型 
	 * @throws
	 */
	public List<TtAsWrModelItemPO> getWgroupId(String modelId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from tt_as_wr_MODEL_ITEM\n");
		sql.append("WHERE model_id=" + modelId + "\n");
		sql
				.append("and WRGROUP_ID in (select WRGROUP_ID from tt_as_wr_model_group where wrgroup_type="
						+ Constant.WR_MODEL_GROUP_TYPE_01 + ")");
		List<TtAsWrModelItemPO> list = select(TtAsWrModelItemPO.class, sql
				.toString(), null);
		return list;
	}

	/**
	 * 
	 * @Title: getVin
	 * @Description: TODO(取得VIN码)
	 * @param 设定文件
	 * @return void 返回类型
	 * @throws
	 */
	public PageResult<TmVehicleExtPO> getVin(Map<String, String> map,
			int pageSize, int curPage) {

		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT V.*,c.ctm_name as customer_name,vw.brand_name as brand_name,vw.brand_code as brand_code,vw.series_name AS series_name,vw.series_code AS series_code,vw.model_name AS model_name,vw.model_name AS model_code,a.CONSIGNATION_DATE as purchased_date_act FROM TM_VEHICLE V \n");
		sql.append(" LEFT OUTER JOIN VW_MATERIAL_GROUP_service vw ON vw.package_id=v.package_id \n");
		sql.append(" LEFT OUTER JOIN TT_DEALER_ACTUAL_SALES A ON V.VEHICLE_ID=A.VEHICLE_ID \n");
		sql.append(" LEFT OUTER JOIN TT_CUSTOMER C ON A.CTM_ID=C.CTM_ID \n");
		//sql.append(" LEFT OUTER JOIN TT_DEALER_ACTUAL_SALES A ON A.VEHICLE_ID=V.VEHICLE_ID \n");
		/*sql
				.append(" LEFT OUTER JOIN TT_ACTUAL_SALES A on A.VEHILCE_ID=V.VEHICLE_ID \n");*/
		sql.append(" WHERE 1=1 ");
		
		if (Utility.testString(map.get("companyId"))) {
			sql.append(" and v.OEM_COMPANY_ID = '" + map.get("companyId")
					+ "' ");
		}
		if (Utility.testString(map.get("vinParent"))) {
			sql.append(" and v.VIN  = '" + map.get("vinParent")
					+ "' ");
		}
		if (Utility.testString(map.get("vin"))) {
			sql.append(" and v.VIN LIKE '%" + map.get("vin") + "%' ");
		}
		/*if (Utility.testString(map.get("customer"))) {
			sql.append(" and a.CUSTOMER_NAME LIKE '%" + map.get("customer")
					+ "%' ");
		}*/
		PageResult<TmVehicleExtPO> ps = pageQuery(TmVehicleExtPO.class, sql
				.toString(), null, pageSize, curPage);
		return ps;

	}
	
	@SuppressWarnings("unchecked")
	public TtAsWrInformationqualityPO getQualityCard(String claimId){
		List<Object> params = new LinkedList<Object>();
		String sql="select * from TT_AS_WR_INFORMATIONQUALITY where CLAIM_ID=?"; 
		params.add(claimId);
		List<TtAsWrInformationqualityPO> resList = this.select(TtAsWrInformationqualityPO.class, sql.toString(), params);
		if(resList!=null)
		{
			if(resList.size()>0) return resList.get(0);
			else return null;
		}
		else return null;
	}
	
	@SuppressWarnings("unchecked")
	public void qualityUpdate(TtAsWrInformationqualityPO po)
	{
		TtAsWrInformationqualityPO selpo=new TtAsWrInformationqualityPO();
		selpo.setId(po.getId());
		update(selpo,po);
	}

	/**
	 * 
	 * @Title: queryChngCodeByType
	 * @Description: TODO(这里用一句话描述这个方法的作用)
	 * @param
	 * @param typeCode
	 * @param
	 * @return 设定文件
	 * @return List<TmBusinessChngCodePO> 返回类型
	 * @throws
	 */
	public List<TmBusinessChngCodePO> queryChngCodeByType(int typeCode,
			Long companyId,String code,String codeName) {
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select t.* from tm_business_chng_code t where 1=1 ");
		sqlStr.append(" and T.type_code = " + typeCode + "");
		if (Utility.testString(companyId.toString())) {
			sqlStr.append(" and T.OEM_COMPANY_ID = '" + companyId + "' \n");
		}
		if (Utility.testString(code)) {
			sqlStr.append(" and T.CODE LIKE '%" + code + "%' \n");
		}
		if (Utility.testString(codeName)) {
			sqlStr.append(" and T.CODE_NAME LIKE '%" + codeName + "%' \n");
		}
		List<TmBusinessChngCodePO> list = select(TmBusinessChngCodePO.class,
				sqlStr.toString(), null);
		return list;
	}
	/**
	 * 
	* @Title: queryChngCodeByType 
	* @Description: TODO(这里用一句话描述这个方法的作用) 
	* @param @param typeCode
	* @param @param companyId
	* @param @param code
	* @param @param codeName
	* @param @param curPage
	* @param @param pageSize
	* @param @return    设定文件 
	* @return PageResult<TmBusinessChngCodePO>    返回类型 
	* @throws
	 */
	public PageResult<TmBusinessChngCodePO> queryChngCodeByType(int typeCode,
			Long companyId,String code,String codeName,int curPage,int pageSize) {
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select t.* from tm_business_chng_code t where 1=1 ");
		sqlStr.append(" and T.type_code = " + typeCode + "");
		if (Utility.testString(companyId.toString())) {
			sqlStr.append(" and T.OEM_COMPANY_ID = '" + companyId + "' \n");
		}
		if (Utility.testString(code)) {
			sqlStr.append(" and T.CODE LIKE '%" + code + "%' \n");
		}
		if (Utility.testString(codeName)) {
			sqlStr.append(" and T.CODE_NAME LIKE '%" + codeName + "%' \n");
		}
		PageResult<TmBusinessChngCodePO> list = pageQuery(TmBusinessChngCodePO.class,
				sqlStr.toString(), null,pageSize,curPage);
		return list;
	}
	
	//zhumingwei 2011-02-12
	public PageResult<Map<String, Object>> queryChngCodeByType111(String code,String codeName,String CUSTOMERS_PROBLEM,int curPage,int pageSize) {
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select T.* from tm_ccc T where 1=1 ");
		if (Utility.testString(code)) {
			sqlStr.append(" and T.vrt_code LIKE '%" + code + "%' \n");
		}
		if (Utility.testString(codeName)) {
			sqlStr.append(" and T.vfg_NAME LIKE '%" + codeName + "%' \n");
		}
		if (Utility.testString(CUSTOMERS_PROBLEM)) {
			sqlStr.append(" and T.ccc_name LIKE '%" + CUSTOMERS_PROBLEM + "%' \n");
		}
		PageResult<Map<String, Object>> ps = pageQuery(sqlStr.toString(), null, getFunName(), pageSize, curPage);
		return ps;
	}
	
	//zhumingwei 2011-03-08
	public List<Map<String, Object>> queryCon(String code) {
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select distinct a.vfg_name from tm_ccc a where a.vrt_code='"+code+"'");
		List<Map<String, Object>> list = pageQuery(sqlStr.toString(), null, getFunName());
		return list;
	}

	/**
	 * 
	 * @Title: queryPartCodeByItemId
	 * @Description: TODO(根据工时ID取故障代码，下拉框联动)
	 * @param
	 * @param user
	 * @param
	 * @param con
	 * @param
	 * @param pageSize
	 * @param
	 * @param curPage
	 * @param
	 * @return 设定文件
	 * @return PageResult<TmPtPartBaseExtPO> 返回类型
	 * @throws
	 */
	public PageResult<TmBusinessChngCodePO> queryPartCodeByItemId(int typeCode,
			String itemId,String code,String codeName,int curPage,int pageSize) {
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append(" select * from tm_business_chng_code b  ");
		//sqlStr
		//		.append(" left outer join TT_AS_WR_TROBLE_MAP m on b.business_code_id = m.trouble_id ");
		//sqlStr
		//		.append(" left outer join TT_AS_WR_WRLABINFO l on m.labor_id=l.id ");
		sqlStr.append(" where 1=1 ");
		//sqlStr.append(" and l.tree_code = '3' ");
		sqlStr.append(" and b.type_code = " + typeCode + " ");
		//sqlStr.append(" and l.labour_code ='" + itemId + "' ");
		if (Utility.testString(code)) {
			sqlStr.append(" and b.CODE LIKE '%" + code + "%' \n");
		}
		if (Utility.testString(codeName)) {
			sqlStr.append(" and b.CODE_NAME LIKE '%" + codeName + "%' \n");
		}
		
		StringBuffer sqlStr0 = new StringBuffer();
		sqlStr0.append(" select * from tm_business_chng_code b  ");
		//sqlStr0
		//		.append(" left outer join TT_AS_WR_TROBLE_MAP m on b.business_code_id = m.trouble_id ");
		//sqlStr0
		//		.append(" left outer join TT_AS_WR_WRLABINFO l on m.labor_id=l.id ");
		sqlStr0.append(" where 1=1 ");
		//sqlStr0.append(" and l.tree_code = '3' ");
		//sqlStr0.append(" and b.type_code = " + typeCode + " ");
		//sqlStr.append(" and l.labour_code ='" + itemId + "' ");
		if (Utility.testString(code)) {
			sqlStr0.append(" and b.CODE LIKE '%" + code + "%' \n");
		}
		if (Utility.testString(codeName)) {
			sqlStr0.append(" and b.CODE_NAME LIKE '%" + codeName + "%' \n");
		}
		PageResult<TmBusinessChngCodePO> list = pageQuery(TmBusinessChngCodePO.class,
				sqlStr.toString(), null,pageSize,curPage);
		if (list.getTotalRecords()>0) {
			
		}else {
			list = pageQuery(TmBusinessChngCodePO.class,
					sqlStr0.toString(), null,pageSize,curPage);
		}
		return list;
	}
	
	/**
	 * 
	 * @Title: queryPartCodeByItemId
	 * @Description: TODO(根据工时ID取故障代码，下拉框联动)
	 * @param
	 * @param user
	 * @param
	 * @param con
	 * @param
	 * @param pageSize
	 * @param
	 * @param curPage
	 * @param
	 * @return 设定文件
	 * @return PageResult<TmPtPartBaseExtPO> 返回类型
	 * @throws
	 */
	public List<TmBusinessChngCodePO> queryPartCodeByItemId(int typeCode,
			String itemId,String code,String codeName) {
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append(" select * from tm_business_chng_code b  ");
		sqlStr
				.append(" left outer join TT_AS_WR_TROBLE_MAP m on b.business_code_id = m.trouble_id ");
		sqlStr
				.append(" left outer join TT_AS_WR_WRLABINFO l on m.labor_id=l.id ");
		sqlStr.append(" where 1=1 ");
		sqlStr.append(" and l.tree_code = '3' ");
		sqlStr.append(" and b.type_code = " + typeCode + " ");
		sqlStr.append(" and l.labour_code ='" + itemId + "' ");
		if (Utility.testString(code)) {
			sqlStr.append(" and b.CODE LIKE '%" + code + "%' \n");
		}
		if (Utility.testString(codeName)) {
			sqlStr.append(" and b.CODE_NAME LIKE '%" + codeName + "%' \n");
		}
		List<TmBusinessChngCodePO> list = select(TmBusinessChngCodePO.class,
				sqlStr.toString(), null);
		return list;
	}
	/**
	 * 
	* @Title: querySupplier 
	* @Description: TODO(这里用一句话描述这个方法的作用) 
	* @param @param map
	* @param @param curPage
	* @param @param pageSize
	* @param @return    设定文件 
	* @return PageResult<TmPtSupplierPO>    返回类型 
	* @throws
	 */
	public PageResult<TmPtSupplierPO> querySupplier(Map<String,String> map,int curPage,int pageSize) {
		StringBuffer sql= new StringBuffer("\n");
		sql.append("select c.supplier_id as supplier_id, supplier_code, supplier_name\n" );
		sql.append("             from TM_PT_SUPPLIER c\n");
		
		if (Integer.valueOf(map.get("len"))==0) {
			sql.append(" where 1=2\n");
		}
		if (Integer.valueOf(map.get("len"))!=0) {
			sql.append(" where 1=1\n");
		}	
		if (Utility.testString(map.get("companyId"))) {
			//sql.append("  AND A.OEM_COMPANY_ID = '"+map.get("companyId")+"'\n" );
		}
		if (Integer.valueOf(map.get("len"))!=0) {
			if (Utility.testString(map.get("partCode"))) {
				sql.append("and c.supplier_id in (\n");
				sql.append("select sr.supplier_id from TM_PT_PART_SUP_RELATION sr where sr.order_id  in (\n" );
				sql.append("select pb.part_id from  tm_pt_part_base pb where pb.part_code='"+map.get("partCode")+"'\n" );
				sql.append(")\n" );
				sql.append(")");
			}
		}
		
		if (Utility.testString(map.get("supplierCode"))) {
			sql.append("  AND lower(c.SUPPLIER_CODE) like '%"+map.get("supplierCode").toLowerCase()+"%' \n");
		}
		if (Utility.testString(map.get("supplierName"))) {
			sql.append("  AND c.SUPPLIER_NAME like '%"+map.get("supplierName")+"%' \n");
		}
		if (Integer.valueOf(map.get("len"))!=0) {
			sql.append("union all select ps.supplier_id,ps.supplier_code,ps.supplier_name from TM_PT_SUPPLIER ps where ps.supplier_name ='未知'");	
			
		}
		if (Integer.valueOf(map.get("len"))==0) {
		sql.append("union all select ps.supplier_id,ps.supplier_code,ps.supplier_name from TM_PT_SUPPLIER ps where ps.supplier_name ='无' \n");
		}

		PageResult<TmPtSupplierPO> ps = pageQuery(TmPtSupplierPO.class,sql.toString(), null, pageSize, curPage);
	
		return ps;
	}

	/**
	 * 
	 * @Title: queryChngCodeByType
	 * @Description: TODO(车型组下拉框)
	 * @param
	 * @param typeCode
	 * @param
	 * @return 设定文件
	 * @return List<TmBusinessChngCodePO> 返回类型
	 * @throws
	 */
	public List<TtAsWrModelGroupPO> queryGroupName() {
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select t.* from TT_AS_WR_MODEL_GROUP t where 1=1 ");
		sqlStr.append(" and WRGROUP_TYPE=" + Constant.WR_MODEL_GROUP_TYPE_01
				+ " "); // 索赔工时车型组
		// sqlStr.append(" and T.type_code = "+typeCode+"");
		List<TtAsWrModelGroupPO> list = select(TtAsWrModelGroupPO.class, sqlStr
				.toString(), null);
		return list;
	}

	/**
	 * 
	 * @Title: queryOtherFee
	 * @Description: TODO(其他费用下拉框)
	 * @param
	 * @return 设定文件
	 * @return List<TtAsWrModelGroupPO> 返回类型 
	 * @throws
	 */
	public List<TtAsWrOtherfeePO> queryOtherFee(Long companyId) {
		StringBuffer sqlStr = new StringBuffer();
		sqlStr
				.append("select t.* from TT_AS_WR_OTHERFEE t where 1=1 AND t.IS_DEL=0 AND t.OEM_COMPANY_ID="
						+ companyId + " ORDER BY FEE_ID ASC");
		// sqlStr.append(" and T.type_code = "+typeCode+"");
		List<TtAsWrOtherfeePO> list = select(TtAsWrOtherfeePO.class, sqlStr
				.toString(), null);
		return list;
	}

	/**
	 * 
	 * @Title: queryOtherFee
	 * @Description: TODO(其他费用下拉框)
	 * @param
	 * @return 设定文件
	 * @return List<TtAsWrModelGroupPO> 返回类型 
	 * @throws
	 */
	public List<TtAsWrOtherfeePO> queryOtherFee(Long companyId,String fee_code) {
		StringBuffer sqlStr = new StringBuffer();
		sqlStr
				.append("select t.* from TT_AS_WR_OTHERFEE t where 1=1 AND t.IS_DEL=0 and fee_code='"+fee_code+"' AND t.OEM_COMPANY_ID="
						+ companyId + " ORDER BY FEE_ID ASC");
		// sqlStr.append(" and T.type_code = "+typeCode+"");
		List<TtAsWrOtherfeePO> list = select(TtAsWrOtherfeePO.class, sqlStr
				.toString(), null);
		return list;
	}
	/**
	 * 
	* @Title: changeOtherFore 
	* @Description: TODO(查询其他项目是否授权) 
	* @param @param map
	* @param @return    设定文件 
	* @return List    返回类型 
	* @throws
	 */
	public List changeOtherFore(Map<String,String> map) {
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select count(*) as is_del \n" );
		sqlStr.append("from TT_AS_WR_FOREAPPROVAL t1,tm_dealer td,TT_AS_WR_FOREAPPROVALITEM t2\n" );
		sqlStr.append("where t1.dealer_id = td.dealer_id\n" );
		sqlStr.append("and t2.status is not null\n" );
		sqlStr.append("and t1.OEM_COMPANY_ID ="+map.get("companyId")+"\n" );
		sqlStr.append("and t1.id = t2.fid\n" );
		sqlStr.append("and t1.ro_no='"+map.get("roNo")+"'\n" );
		sqlStr.append("and t1.vin='"+map.get("vin")+"'\n" );
		sqlStr.append("and t2.status="+Constant.PRECLAIM_AUDIT_01+"\n" ); //已同意
		sqlStr.append("and t2.item_type="+Constant.PRE_AUTH_ITEM_03+"\n" ); //其他项目
		sqlStr.append("and t2.item_code='"+map.get("itemCode")+"'\n" );
		List<TtAsWrOtherfeePO> list = select(TtAsWrOtherfeePO.class, sqlStr
				.toString(), null);
		return list;
	}
	/**
	 * 
	* @Title: getLabourParameter 
	* @Description: TODO(取工时单价) 
	* @param @param dealerId
	* @param @param modelId
	* @param @param companyId
	* @param @return    设定文件 
	* @return String    返回类型 
	* @throws
	 */
	public String getLabourParameter(String dealerId,String modelId,String companyId) {
		StringBuffer sql= new StringBuffer();
		sql.append("SELECT pr.labour_price as labour_price \n" );
		sql.append(" from tt_as_wr_labour_price pr\n" );
		sql.append("left outer join tm_dealer td on td.dealer_id = pr.dealer_id\n" );	
		sql.append("where 1=1   and pr.oem_company_id = "+companyId+"\n" );
		sql.append("AND pr.DEALER_ID="+dealerId+"\n" );
		sql.append(" and pr.mode_type =\n" );
		sql.append(" (SELECT WRGROUP_CODE FROM tt_as_wr_model_group\n" );
		sql.append(" WHERE WRGROUP_ID in (SELECT WRGROUP_ID FROM tt_as_wr_model_item WHERE MODEL_ID="+modelId+") and wrgroup_type="+Constant.WR_MODEL_GROUP_TYPE_01+")");
		return sql.toString();
	}
	/**
	 * 
	* @Title: getLabourPrice 
	* @Description: TODO(取工时单价) 
	* @param @param dealerId
	* @param @param modelId
	* @param @param companyId
	* @param @return    设定文件 
	* @return String    返回类型 
	* @throws
	 */
	public String getLabourPrice(String dealerId,String modelCode,String companyId) {
		String modelId = "";
		if (Utility.testString(modelCode)) {

			VwMaterialGroupPO vmgp = new VwMaterialGroupPO();
			vmgp.setModelCode(modelCode);
			List<VwMaterialGroupPO> ls = dao.select(vmgp);
			if (ls != null) {
				if (ls.size() > 0) {
					vmgp = ls.get(0);
					modelId = vmgp.getModelId() == null ? "" : vmgp
							.getModelId().toString();
				}
			}
			String a = "0";
			String sql = "select nvl(labour_price,0) as labour_price from ("
					+ getLabourParameter(dealerId, modelId, companyId) + ") t";
			Map<String, Object> map = pageQueryMap(sql, null, getFunName());
			if (map != null && map.size() > 0) {
				a = String.valueOf(map.get("LABOUR_PRICE"));
			}
			return a;
		} else {
			return "0";
		}
	}
	/**
	 * 
	 * @Title: queryTime
	 * @Description: TODO(查询工时)
	 * @param
	 * @param user
	 * @param
	 * @param con
	 * @param
	 * @param pageSize
	 * @param
	 * @param curPage
	 * @param
	 * @return 设定文件
	 * @return PageResult<TtAsWrWrlabinfoExtPO> 返回类型 
	 * @throws
	 */
	public PageResult<TtAsWrWrlabinfoExtPO> queryTime(AclUserBean user,
			String con,String roNo,String vin ,String modelId,String companyId,int pageSize, int curPage) {
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append(" select g.WRGROUP_NAME AS wrgroup_name,t.*,t.labour_quotiety*t.labour_hour as labour_fix, " );
		sqlStr.append("(case when instr(t.labour_code,'"+Constant.SPEC_LABOUR_CODE+"')=1 then 1 else 0 end ) as is_spec,");
		sqlStr.append("nvl(("+getLabourParameter(user.getDealerId(),modelId,companyId)+" ),0) AS parameter_value from TT_AS_WR_WRLABINFO t ");
		sqlStr.append(" LEFT OUTER JOIN TT_AS_WR_MODEL_GROUP G ON T.WRGROUP_ID=G.WRGROUP_ID  ");
		sqlStr.append(" where 1=1  ");
		sqlStr.append(" and t.is_send=" + Constant.DOWNLOAD_CODE_STATUS_03
				+ " ");
		sqlStr.append(" and t.IS_DEL=0 ");
		// sqlStr.append(" and d.DEALER_ID="+user.getDealerId()+" ");
		// sqlStr.append(" and d.PARAMETER_CODE='"+10421001+"' ");
		sqlStr.append(con);
		PageResult<TtAsWrWrlabinfoExtPO> ps = pageQuery(
				TtAsWrWrlabinfoExtPO.class, sqlStr.toString(), null, pageSize,
				curPage);
		return ps;
	}

	/**
	 * 
	 * @Title: queryPartCode
	 * @Description: TODO(查询配件)
	 * @param
	 * @param user
	 * @param
	 * @param con
	 * @param
	 * @param pageSize
	 * @param
	 * @param curPage
	 * @param
	 * @return 设定文件
	 * @return PageResult<TmPtPartBaseExtPO> 返回类型
	 * @throws
	 */
	public PageResult<TmPtPartBaseExtPO> queryPartCode(AclUserBean user,
		      Map<String, String> map, int pageSize, int curPage) {
		    StringBuffer sqlStr = new StringBuffer();
		    sqlStr.append("select DISTINCT (A.PART_code) as pars,\n");
		    sqlStr.append("                a.*,\n" );
		    List<Map<String, Object>> list = selectPrice();
		    String price = list.get(0).get("PARA_VALUE").toString();
		    if(list.get(0).get("PARA_NAME").toString().equals("是")){
		        
		      sqlStr.append("case\n");
		      sqlStr.append("        when (nvl(a.claim_price, 0) * (1 + nvl(m.parameter_value, 0) / 100) - a.claim_price) > "+price+" then\n" );
		      sqlStr.append("         claim_price + "+price+"\n" );
		      sqlStr.append("        else\n" );
		      sqlStr.append("         nvl(a.claim_price, 0) * (1 + nvl(m.parameter_value, 0) / 100)\n" );
		      sqlStr.append("      end as claim_Price_param");
		    }else{
		      sqlStr.append("                nvl(a.claim_price, 0) *\n" );
		      sqlStr.append("                (1 + nvl(m.parameter_value, 0) / 100) as claim_Price_param\n" );
		    }
		    sqlStr.append("  from TM_PT_PART_BASE a, tm_down_parameter m\n" );
		    sqlStr.append(" where 1 = 1");
		    sqlStr.append(" and m.dealer_id="+user.getDealerId()+"");
		    // if (Utility.testString(map.get("groupId"))) {
		    /* 此处屏蔽掉车型过滤 */
		    // sqlStr.append(" AND A.GROUP_ID =" + map.get("groupId") + " ");
		    // }
		    if (Utility.testString(map.get("companyId"))) {
		      sqlStr.append(" AND A.OEM_COMPANY_ID = '" + map.get("companyId")
		          + "' ");
		    }
		    if (Utility.testString(map.get("partCode"))) {
		      sqlStr.append(" AND A.PART_CODE LIKE '%" + map.get("partCode")
		          + "%' ");
		    }
		    if (Utility.testString(map.get("partName"))) {
		      sqlStr.append(" AND A.PART_NAME LIKE '%" + map.get("partName")
		          + "%' ");
		    }
		 
		    //sqlStr.append(" GROUP ");
		    // sqlStr.append(con);
		    PageResult<TmPtPartBaseExtPO> ps = pageQuery(TmPtPartBaseExtPO.class,
		        sqlStr.toString(), null, pageSize, curPage);
		    return ps;
		  }
  
	public PageResult<TmPtPartBaseExtPO> queryPartCode1(AclUserBean user,
		      Map<String, String> map, int pageSize, int curPage,String downPartCode) {
		    StringBuffer sqlStr = new StringBuffer();
		    sqlStr.append("select DISTINCT (A.PART_code) as pars,\n");
		    sqlStr.append("                a.*,\n" );
		    List<Map<String, Object>> list = selectPrice();
		    String price = list.get(0).get("PARA_VALUE").toString();
		    if(list.get(0).get("PARA_NAME").toString().equals("是")){
		        
		      sqlStr.append("case\n");
		      sqlStr.append("        when (nvl(a.claim_price, 0) * (1 + nvl(m.parameter_value, 0) / 100) - a.claim_price) > "+price+" then\n" );
		      sqlStr.append("         claim_price + "+price+"\n" );
		      sqlStr.append("        else\n" );
		      sqlStr.append("         nvl(a.claim_price, 0) * (1 + nvl(m.parameter_value, 0) / 100)\n" );
		      sqlStr.append("      end as claim_Price_param");
		    }else{
		      sqlStr.append("                nvl(a.claim_price, 0) *\n" );
		      sqlStr.append("                (1 + nvl(m.parameter_value, 0) / 100) as claim_Price_param\n" );
		    }
		    sqlStr.append("  from TM_PT_PART_BASE a, tm_down_parameter m\n" );
		    sqlStr.append(" where 1 = 1");
		    sqlStr.append(" and m.dealer_id="+user.getDealerId()+"");
		    // if (Utility.testString(map.get("groupId"))) {
		    /* 此处屏蔽掉车型过滤 */
		    // sqlStr.append(" AND A.GROUP_ID =" + map.get("groupId") + " ");
		    // }
		    if (Utility.testString(map.get("companyId"))) {
		      sqlStr.append(" AND A.OEM_COMPANY_ID = '" + map.get("companyId")
		          + "' ");
		    }
		    if (Utility.testString(map.get("partCode"))) {
		      sqlStr.append(" AND A.PART_CODE LIKE '%" + map.get("partCode")
		          + "%' ");
		    }
		    if (Utility.testString(map.get("partName"))) {
		      sqlStr.append(" AND A.PART_NAME LIKE '%" + map.get("partName")
		          + "%' ");
		    }
		    List<Map<String,Object>> ListCode =   dao.queryTcCode();
		    Integer code = Integer.valueOf(ListCode.get(0).get("CODE_ID").toString());
		    if(code==Constant.chana_wc){
		      sqlStr.append("  and exists (select s.part_type_id from TM_PT_PART_BASE s where s.part_code='"+downPartCode+"' and a.part_type_id = s.part_type_id)  ");
		    }
		    //sqlStr.append(" GROUP ");
		    // sqlStr.append(con);
		    PageResult<TmPtPartBaseExtPO> ps = pageQuery(TmPtPartBaseExtPO.class,
		        sqlStr.toString(), null, pageSize, curPage);
		    return ps;
		  }

	
	  public List<Map<String,Object>> queryTcCode() {
			String sql = "select * from tc_code where type=8008";
			List<Map<String,Object>> ps = this.pageQuery(sql, null, this.getFunName());
			return ps;
		}
	public PageResult<TtAsWrApplicationExtBean> queryDealerApplication(
			AclUserBean user, Map<String, String> map, List params,
			int pageSize, int curPage) {
    	 
    	String yieldly = map.get("yieldly");
		StringBuffer sqlStr = new StringBuffer();

		sqlStr.append("SELECT A.*,\n" );
		sqlStr.append("       B.DEALER_CODE AS DEALER_CODE,\n" );
		sqlStr.append("       B.DEALER_NAME AS DEALER_NAME,\n" );
		sqlStr.append("       V.DELIVERER,\n" );
		sqlStr.append("       V.DELIVERER_PHONE\n" );
		sqlStr.append("  FROM TT_AS_REPAIR_ORDER V, TT_AS_WR_APPLICATION A, TM_DEALER B\n" );
		sqlStr.append(" WHERE 1 = 1\n" );
		sqlStr.append("   AND B.DEALER_ID = A.DEALER_ID + 0\n" );
		sqlStr.append("   AND A.RO_NO = V.RO_NO(+)\n" );
		if(yieldly!=null){
			sqlStr.append("   AND A.yieldly = '"+yieldly+"'\n" );
		}
		if (Utility.testString(map.get("oemCompanyId"))) {
			sqlStr.append(" AND A.OEM_COMPANY_ID='" + map.get("oemCompanyId")
					+ "' \n");
		}
		if (Utility.testString(map.get("dealerName"))) {
			sqlStr.append(" AND b.DEALER_name like'%" + map.get("dealerName")
					+ "%' \n");
		}
		if (Utility.testString(map.get("dealerCode"))) {
			sqlStr.append(Utility.getConSqlByParamForEqual(map
					.get("dealerCode"), params, "b", "dealer_code"));
		}
		// 经销商代码
		if (Utility.testString(map.get("dealerId"))) {
			sqlStr.append(" AND A.DEALER_ID='" + map.get("dealerId") + "' \n");
		}
		// 索赔单号
		if (Utility.testString(map.get("claimNo"))) {
			sqlStr.append(" AND A.CLAIM_NO LIKE '%" + map.get("claimNo")
					+ "%' \n");
		}
		// 工单号
		if (Utility.testString(map.get("roNo"))) {
			sqlStr.append(" AND A.RO_NO LIKE '%" + map.get("roNo") + "%' \n");
		}
		// 行号
		if (Utility.testString(map.get("lineNo"))) {
			sqlStr.append(" AND A.LINE_NO='" + map.get("lineNo") + "' \n");
		}
		// 索赔类型
		if (Utility.testString(map.get("claimType"))) {
			sqlStr
					.append(" AND A.CLAIM_TYPE='" + map.get("claimType")
							+ "' \n");
		}
		// 车辆VIN码
		if (Utility.testString(map.get("vin"))) {// VIN涓嶄负绌 
			sqlStr.append(GetVinUtil.getVins(map.get("vin").replaceAll("'",
					"\''"), "A"));
		}
		// 工单开始时间
		if (Utility.testString(map.get("roStartdate"))) {
			sqlStr
					.append(" AND A.CREATE_DATE >= to_date('"
							+ map.get("roStartdate")
							+ "', 'yyyy-mm-dd hh24:mi:ss') \n");
		}
		// 工单结束时间
		if (Utility.testString(map.get("roEnddate"))) {
			sqlStr.append(" AND A.CREATE_DATE <= to_date('"
					+ map.get("roEnddate")
					+ " 23:59:59', 'yyyy-mm-dd hh24:mi:ss') \n");
		}
		// 审核时间查询条件
		if(Utility.testString(map.get("approveDate"))){
			sqlStr.append(" AND a.auditing_date >= to_date('"
					+map.get("approveDate")
					+"','yyyy-MM-dd hh24:mi:ss') \n");
		}
		if(Utility.testString(map.get("approveDate2"))){
			sqlStr.append(" AND a.auditing_date <= to_date('"
					+map.get("approveDate2")
					+" 23:59:59','yyyy-MM-dd hh24:mi:ss')\n");
		}
		// 申请状态
		if (Utility.testString(map.get("status"))) {
			sqlStr.append(" AND A.STATUS='" + map.get("status") + "' \n");

		} else if (!"track".equals(map.get("track"))) {
			sqlStr.append(" AND (A.STATUS='" + Constant.CLAIM_APPLY_ORD_TYPE_01
					+ "'  \n");
			sqlStr.append(" OR A.STATUS='" + Constant.CLAIM_APPLY_ORD_TYPE_06
					+ "')  \n");

		} else if (!"isApp".equals(map.get("isApp"))) {
			sqlStr.append(" AND (A.STATUS<" + Constant.CLAIM_APPLY_ORD_TYPE_01
					+ " OR A.STATUS >" + Constant.CLAIM_APPLY_ORD_TYPE_01 +") \n");
		}
		if (Utility.testString(map.get("deliverer"))) {
			sqlStr.append(" and v.deliverer like '%"+map.get("deliverer")+"%' ");
		}
		if (Utility.testString(map.get("delivererPhone"))) {
			sqlStr.append(" and v.deliverer_phone like '%"+map.get("delivererPhone")+"%' ");
		}
		sqlStr.append(" ORDER BY A.ID DESC ");
		System.out.println("sqlsql=="+sqlStr.toString());
		logger.info("-----------------------"+sqlStr.toString());
		PageResult<TtAsWrApplicationExtBean> ps = pageQuery(
				TtAsWrApplicationExtBean.class, sqlStr.toString(), params,
				pageSize, curPage);
		return ps;
	}
    
    public PageResult<TtAsWrApplicationExtBean> queryDealerDeleteApplication(
			AclUserBean user, Map<String, String> map, List params,
			int pageSize, int curPage) {
    	 
    	String yieldly = map.get("yieldly");
    	String yieldlys = map.get("yieldlys");
		StringBuffer sqlStr = new StringBuffer();

		sqlStr.append("SELECT A.*,\n" );
		sqlStr.append("       B.DEALER_CODE AS DEALER_CODE,\n" );
		sqlStr.append("       B.DEALER_NAME AS DEALER_NAME,\n" );
		sqlStr.append("       V.DELIVERER,\n" );
		sqlStr.append("       V.DELIVERER_PHONE\n" );
		sqlStr.append("  FROM TT_AS_REPAIR_ORDER V, TT_AS_WR_APPLICATION A, TM_DEALER B\n" );
		sqlStr.append(" WHERE 1 = 1 and v.ORDER_VALUABLE_TYPE='"+Constant.RO_PRO_STATUS_02+"' and A.status in('"+Constant.CLAIM_APPLY_ORD_TYPE_01+"','"+Constant.CLAIM_APPLY_ORD_TYPE_04+"','"+Constant.CLAIM_APPLY_ORD_TYPE_05+"','"+Constant.CLAIM_APPLY_ORD_TYPE_06+"')\n" );
		sqlStr.append("   AND B.DEALER_ID = A.DEALER_ID + 0\n" );
		sqlStr.append("   AND A.RO_NO = V.RO_NO(+)\n" );
		if (Utility.testString(yieldly)) {
			sqlStr.append(" AND A.YIELDLY='" + yieldly + "' \n");
		}
		if (Utility.testString(map.get("oemCompanyId"))) {
			sqlStr.append(" AND A.OEM_COMPANY_ID='" + map.get("oemCompanyId")
					+ "' \n");
		}
		if (Utility.testString(map.get("dealerName"))) {
			sqlStr.append(" AND b.DEALER_name like'%" + map.get("dealerName")
					+ "%' \n");
		}
		if (Utility.testString(map.get("dealerCode"))) {
			sqlStr.append(Utility.getConSqlByParamForEqual(map
					.get("dealerCode"), params, "b", "dealer_code"));
		}
		// 经销商代码
		if (Utility.testString(map.get("dealerId"))) {
			sqlStr.append(" AND A.DEALER_ID='" + map.get("dealerId") + "' \n");
		}
		// 索赔单号
		if (Utility.testString(map.get("claimNo"))) {
			sqlStr.append(" AND A.CLAIM_NO LIKE '%" + map.get("claimNo")
					+ "%' \n");
		}
		// 工单号
		if (Utility.testString(map.get("roNo"))) {
			sqlStr.append(" AND A.RO_NO LIKE '%" + map.get("roNo") + "%' \n");
		}
		// 行号
		if (Utility.testString(map.get("lineNo"))) {
			sqlStr.append(" AND A.LINE_NO='" + map.get("lineNo") + "' \n");
		}
		// 索赔类型
		if (Utility.testString(map.get("claimType"))) {
			sqlStr
					.append(" AND A.CLAIM_TYPE='" + map.get("claimType")
							+ "' \n");
		}
		// 车辆VIN码
		if (Utility.testString(map.get("vin"))) {// VIN涓嶄负绌�
			sqlStr.append(GetVinUtil.getVins(map.get("vin").replaceAll("'",
					"\''"), "A"));
		}
		// 工单开始时间
		if (Utility.testString(map.get("roStartdate"))) {
			sqlStr
					.append(" AND A.CREATE_DATE >= to_date('"
							+ map.get("roStartdate")
							+ "', 'yyyy-mm-dd hh24:mi:ss') \n");
		}
		// 工单结束时间
		if (Utility.testString(map.get("roEnddate"))) {
			sqlStr.append(" AND A.CREATE_DATE <= to_date('"
					+ map.get("roEnddate")
					+ " 23:59:59', 'yyyy-mm-dd hh24:mi:ss') \n");
		}
		// 审核时间查询条件
		if(Utility.testString(map.get("approveDate"))){
			sqlStr.append(" AND a.auditing_date >= to_date('"
					+map.get("approveDate")
					+"','yyyy-MM-dd hh24:mi:ss') \n");
		}
		if(Utility.testString(map.get("approveDate2"))){
			sqlStr.append(" AND a.auditing_date <= to_date('"
					+map.get("approveDate2")
					+" 23:59:59','yyyy-MM-dd hh24:mi:ss')\n");
		}
		// 申请状态
		if (Utility.testString(map.get("status"))) {
			sqlStr.append(" AND A.STATUS='" + map.get("status") + "' \n");

		} else if (!"track".equals(map.get("track"))) {
			sqlStr.append(" AND (A.STATUS='" + Constant.CLAIM_APPLY_ORD_TYPE_01
					+ "'  \n");
			sqlStr.append(" OR A.STATUS='" + Constant.CLAIM_APPLY_ORD_TYPE_06
					+ "')  \n");

		} else if (!"isApp".equals(map.get("isApp"))) {
			sqlStr.append(" AND (A.STATUS<" + Constant.CLAIM_APPLY_ORD_TYPE_01
					+ " OR A.STATUS >" + Constant.CLAIM_APPLY_ORD_TYPE_01 +") \n");
		}
		if (Utility.testString(map.get("deliverer"))) {
			sqlStr.append(" and v.deliverer like '%"+map.get("deliverer")+"%' ");
		}
		if (Utility.testString(map.get("delivererPhone"))) {
			sqlStr.append(" and v.deliverer_phone like '%"+map.get("delivererPhone")+"%' ");
		}
		sqlStr.append(" ORDER BY A.ID DESC ");
		logger.info("-----------------------"+sqlStr.toString());
		System.out.println("sql=="+sqlStr);
		PageResult<TtAsWrApplicationExtBean> ps = pageQuery(
				TtAsWrApplicationExtBean.class, sqlStr.toString(), params,
				pageSize, curPage);
		return ps;
	}
	/**
	 * 
	 * @Title: queryApplication
	 * @Description: TODO(查询索赔单)
	 * @param
	 * @param user
	 * @param
	 * @param con
	 * @param
	 * @param pageSize
	 * @param
	 * @param curPage
	 * @param
	 * @return 设定文件
	 * @return PageResult<TtAsWrApplicationExtPO> 返回类型 
	 * @throws
	 */
     public PageResult<TtAsWrApplicationExtBean> queryApplication1(
			AclUserBean user, Map<String, String> map, List params,
			int pageSize, int curPage) {
    	 
    	String yieldly = map.get("yieldly");
    	String yieldlys = map.get("yieldlys");
		StringBuffer sqlStr = new StringBuffer();

		sqlStr.append("SELECT A.*,\n" );
		sqlStr.append("       B.DEALER_CODE AS DEALER_CODE,\n" );
		sqlStr.append("       B.DEALER_NAME AS DEALER_NAME,\n" );
		sqlStr.append("       V.DELIVERER,\n" );
		sqlStr.append("       V.DELIVERER_PHONE,\n" );
		sqlStr.append("	( select u.name from tc_user u where u.user_id =\n");
		sqlStr.append(" ( select w.create_by from tt_as_wr_appauthitem w where w.create_date = (\n" );
		sqlStr.append("  select max(wa.create_date) from tt_as_wr_appauthitem  wa where  wa.id=a.id)\n" );
		sqlStr.append("  and w.id=a.id AND ROWNUM =1))as auth_Person,");
		sqlStr.append(" (select name from (select * from tt_as_wr_appauthitem ap ");
		sqlStr.append(" left outer join tc_user tu on ap.create_by = tu.user_id order by approval_date desc)");
		sqlStr.append(" where id = a.id  and approval_person like '%授权审核%' and approval_person like '%"+map.get("person")+"%' and rownum = 1) as fore_Auth_Person \n"); //YH 2010.12.15
		sqlStr.append("  FROM TT_AS_REPAIR_ORDER V,TT_AS_WR_APPLICATION A, TM_DEALER B\n" );
		sqlStr.append("  WHERE 1 = 1\n" );
		if(Utility.testString(map.get("person"))){
			//sqlStr.append("	and a.id in(\n");
			//sqlStr.append("	select  id  from  tt_as_wr_appauthitem wa where wa.create_by in(\n" );
			//sqlStr.append(" select u.user_id from tc_user u where u.name like '%"+map.get("person")+"%' ))\n");
			sqlStr.append("and exists (\n");
			sqlStr.append("      select 1 from tt_as_wr_appauthitem wa where wa.id = a.id and exists(\n");  
			sqlStr.append("          select 1 from tc_user u where u.name like '%"+map.get("person")+"%' and u.user_id = wa.create_by\n");  
			sqlStr.append("      )\n");  
			sqlStr.append("  )\n");
		}
		/*if(Utility.testString(map.get("claimNo"))){
		sqlStr.append("  AND A.Claim_No like '%"+map.get("claimNo")+"%' ");
		}*/
		sqlStr.append("   AND B.DEALER_ID = A.DEALER_ID + 0\n" );
		sqlStr.append("   AND A.RO_NO = V.RO_NO(+)\n" );
		//sqlStr.append(" select a.*,b.dealer_code as dealer_code, b.dealer_name as dealer_name,v.deliverer,v.deliverer_phone \n");
		//sqlStr.append(" from TT_AS_WR_APPLICATION a   \n");
		//sqlStr.append(" left outer join tt_as_repair_order v  on a.ro_no=v.ro_no ");
		//sqlStr.append(" left outer join TM_DEALER b on a.dealer_id=b.dealer_id \n");
		//sqlStr.append(" where 1=1 ");
		// 公司ID
		if (Utility.testString(yieldly)) {
			sqlStr.append(" AND A.YIELDLY='" + yieldly + "' \n");
		} else if(Utility.testString(yieldlys)){
			sqlStr.append(" AND A.YIELDLY IN (").append(yieldlys).append(")\n");
		}
		if (Utility.testString(map.get("oemCompanyId"))) {
			sqlStr.append(" AND A.OEM_COMPANY_ID='" + map.get("oemCompanyId")
					+ "' \n");
		}
		if (Utility.testString(map.get("dealerName"))) {
			sqlStr.append(" AND b.DEALER_name like'%" + map.get("dealerName")
					+ "%' \n");
		}
		if (Utility.testString(map.get("dealerCode"))) {
			sqlStr.append(Utility.getConSqlByParamForEqual(map
					.get("dealerCode"), params, "b", "dealer_code"));
		}
		// 经销商代码
		if (Utility.testString(map.get("dealerId"))) {
			sqlStr.append(" AND A.DEALER_ID='" + map.get("dealerId") + "' \n");
		}
		
		/*
		 * if (Utility.testString(id)) { con.append(" AND
		 * A.DEALER_ID='"+dealerId+"' \n"); }
		 */
		
		// 行号
		if (Utility.testString(map.get("lineNo"))) {
			sqlStr.append(" AND A.LINE_NO='" + map.get("lineNo") + "' \n");
		}
		// 索赔类型
		if (Utility.testString(map.get("claimType"))) {
			sqlStr
					.append(" AND A.CLAIM_TYPE='" + map.get("claimType")
							+ "' \n");
		}
		// 工单开始时间
		if (Utility.testString(map.get("roStartdate"))) {
			sqlStr
					.append(" AND A.CREATE_DATE >= to_date('"
							+ map.get("roStartdate")
							+ "', 'yyyy-mm-dd hh24:mi:ss') \n");
		}
		// 工单结束时间
		if (Utility.testString(map.get("roEnddate"))) {
			sqlStr.append(" AND A.CREATE_DATE <= to_date('"
					+ map.get("roEnddate")
					+ " 23:59:59', 'yyyy-mm-dd hh24:mi:ss') \n");
		}
		// 审核时间
		if(Utility.testString(map.get("approveDate"))){
			sqlStr.append(" and a.auditing_date >= to_date('"
					+map.get("approveDate")
					+"','yyyy-MM-dd hh24:mi:ss')\n");
		}
		if(Utility.testString(map.get("approveDate2"))){
			sqlStr.append(" and a.auditing_date <= to_date('"
					+map.get("approveDate2")
					+" 23:59:59','yyyy-MM-dd hh24:mi:ss') \n");
		}
		// 申请状态
		if (Utility.testString(map.get("status"))) {
			sqlStr.append(" AND A.STATUS='" + map.get("status") + "' \n");

		} else if (!"track".equals(map.get("track"))) {
			sqlStr.append(" AND (A.STATUS='" + Constant.CLAIM_APPLY_ORD_TYPE_01
					+ "'  \n");
			// sqlStr.append(" OR A.STATUS='" + Constant.CLAIM_APPLY_ORD_TYPE_05
			// + "' \n");
			sqlStr.append(" OR A.STATUS='" + Constant.CLAIM_APPLY_ORD_TYPE_06
					+ "')  \n");

		} else if (!"isApp".equals(map.get("isApp"))) {
			sqlStr.append(" AND (A.STATUS<" + Constant.CLAIM_APPLY_ORD_TYPE_01
					+ " OR A.STATUS >" + Constant.CLAIM_APPLY_ORD_TYPE_01 +") \n");
		}
		// 索赔单号
		if (Utility.testString(map.get("claimNo"))) {
			sqlStr.append(" AND A.CLAIM_NO LIKE '%" + map.get("claimNo")
					+ "%' \n");
		}
		// 工单号
		if (Utility.testString(map.get("roNo"))) {
			sqlStr.append(" AND A.RO_NO LIKE '%" + map.get("roNo") + "%' \n");
		}
		// 车辆VIN码
		if (Utility.testString(map.get("vin"))) {
			sqlStr.append(GetVinUtil.getVins(map.get("vin").replaceAll("'",
					"\''"), "A"));
		}
		if (Utility.testString(map.get("deliverer"))) {
			sqlStr.append(" and v.deliverer like '%"+map.get("deliverer")+"%' ");
		}
		if (Utility.testString(map.get("delivererPhone"))) {
			sqlStr.append(" and v.deliverer_phone like '%"+map.get("delivererPhone")+"%' ");
		}
		sqlStr.append(" ORDER BY A.ID DESC ");
		logger.info("-----------------------"+sqlStr.toString());
		// sqlStr.append(con);
		PageResult<TtAsWrApplicationExtBean> ps = pageQuery(
				TtAsWrApplicationExtBean.class, sqlStr.toString(), params,
				pageSize, curPage);
		return ps;
	}

	/**
	 * 
	 * @Title: queryApplicationById
	 * @Description: TODO(查询索赔单信息)
	 * @param
	 * @param id
	 * @param
	 * @return 设定文件
	 * @return TtAsWrApplicationExtPO 返回类型
	 * @throws
	 */
	public TtAsWrApplicationExtPO queryApplicationById(String id) {
		StringBuffer sqlStr = new StringBuffer();
		// sqlStr.append(" select a.*,(select brand_name from VW_MATERIAL_GROUP
		// vw where vw.package_id=v.package_id) as brand_name,v.model_id as
		// model_id,g1.group_name as series_name,g2.group_name as
		// model_name,b.dealer_code as dealer_code, b.dealer_name as
		// dealer_name, \n");
		sqlStr.append(" select a.*,aa.activity_name as campaign_name,v.model_id as model_id,v.PURCHASED_DATE as purchased_date,v.product_date,b.dealer_code as dealer_code, b.dealer_name as dealer_name, \n");
		sqlStr.append("o.start_time as start_time,o.end_time as end_time,o.out_person as out_person,\n");
		sqlStr.append("o.out_site as out_site,o.out_licenseno as out_licenseno,o.from_adress as from_adress,\n");
		sqlStr.append("o.end_adress as end_adress,o.out_mileage as out_mileages, \n");
		sqlStr.append("rr.in_mileage as in_mileage ");
		// sqlStr.append(" v.ENGINE_NO as engine_no,v.GEARBOX_NO as
		// gearbox_no,v.REARAXLE_NO as rearaxle_no,v.TRANSFER_NO as
		// transfer_no,v.LICENSE_NO as license_no,v.PURCHASED_DATE as
		// purchased_date \n");
		sqlStr.append(" from TT_AS_WR_APPLICATION a   \n");
		sqlStr.append(" left outer join tt_as_wr_outrepair o on o.ro_no=a.ro_no \n");
		sqlStr.append(" left outer join TM_DEALER b on a.dealer_id=b.dealer_id \n");
		sqlStr.append(" left outer join TM_VEHICLE v on v.vin=a.vin \n");
		sqlStr.append(" left outer join TT_AS_ACTIVITY AA ON AA.ACTIVITY_CODE=A.CAMPAIGN_CODE \n");
		sqlStr.append(" left outer join TT_AS_REPAIR_ORDER RR ON RR.RO_NO=A.RO_NO \n");
		// sqlStr.append(" left outer join TM_VHCL_MATERIAL_GROUP g1 on
		// v.series_id=g1.group_id \n");
		// sqlStr.append(" left outer join TM_VHCL_MATERIAL_GROUP g2 on
		// v.model_id=g2.group_id \n");

		sqlStr.append(" where 1=1 ");
		sqlStr.append(" AND a.ID='" + id + "' ");
		TtAsWrApplicationExtPO tawep = new TtAsWrApplicationExtPO();
		List<TtAsWrApplicationExtPO> ls = select(TtAsWrApplicationExtPO.class,
				sqlStr.toString(), null);
		if (ls != null) {
			if (ls.size() > 0) {
				tawep = ls.get(0);
			}
		}
		return tawep;
	}
	
	/**
	 * 
	* @Title: queryPrintById 
	* @Description: TODO(打印页面查询) 
	* @param @param id
	* @param @return    设定文件 
	* @return TtAsWrApplicationExtPO    返回类型 
	* @throws
	 */
	public TtAsWrApplicationExtPO queryPrintById(String id) {
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append(" select a.*,aa.activity_name as campaign_name,v.model_id as model_id,v.PURCHASED_DATE as purchased_date,b.dealer_code as dealer_code, b.dealer_name as dealer_name, \n");
		sqlStr.append("o.start_time as start_time,o.end_time as end_time,o.out_person as out_person,\n");
		sqlStr.append("o.out_site as out_site,o.out_licenseno as out_licenseno,o.from_adress as from_adress,\n");
		sqlStr.append("o.end_adress as end_adress,o.out_mileage as out_mileages, \n");
		sqlStr.append("rr.in_mileage as in_mileage, (select count(*) from tt_as_repair_order where vin=rr.vin ) as repair_times, \n");
		sqlStr.append("/*tc.ctm_name*/rr.deliverer as customer_name,/*tc.main_phone*/rr.deliverer_phone||' '||rr.deliverer_mobile as customer_phone,tc.address as customer_address, \n");
		sqlStr.append("rr.free_times as free_times, ");
		/********Iverson add By 2010-11-22 添加接待员字段**************/
		sqlStr.append("RR.service_advisor as service_advisor, ");
		/*******Iverson add By 2010-11-22 添加接待员字段******/
		sqlStr.append("(select sum(amount) from tt_as_wr_netitem where id="+id+" and item_code='"+Constant.ROOM_CHARGE+"') as room_charge,\n"); //住宿费
		sqlStr.append("(select sum(amount) from tt_as_wr_netitem where id="+id+" and item_code='"+Constant.EATUP_FEE+"') as eatup_fee,\n"); //餐补费
		sqlStr.append("(select sum(amount) from tt_as_wr_netitem where id="+id+" and item_code='"+Constant.TRANSPORTATION+"') as transportation,\n"); //交通费
		sqlStr.append("(select sum(amount) from tt_as_wr_netitem where id="+id+" and item_code='"+Constant.FAX_FEE+"') as fax_fee,\n"); //电话传真费
		sqlStr.append("(select sum(amount) from tt_as_wr_netitem where id="+id+" and item_code='"+Constant.SUBSIDIES_FEE+"') as subsidies_fee,\n"); //工时补助费
		//sqlStr.append(" max(ap.id),ap.approval_person as auth_person,ap.remark as auth_content \n");
		sqlStr.append("(select name from (select * from tt_as_wr_appauthitem ap left outer join tc_user tu on ap.create_by=tu.user_id order by approval_date desc)  where id=a.id and approval_person like '%授权审核%' and rownum=1) as auth_person,\n" );
		sqlStr.append("(select remark from (select * from tt_as_wr_appauthitem order by approval_date desc) where id=a.id and approval_person like '%授权审核%' and rownum=1 ) as auth_content,");
		sqlStr.append(" vw.package_name as package_name,ts.name as fore_auth_person,fp.opinion as fore_auth_content ");
		sqlStr.append(" from TT_AS_WR_APPLICATION a  \n");
		sqlStr.append(" left outer join tt_as_wr_outrepair o on o.ro_no=a.ro_no \n");
		sqlStr.append(" left outer join TM_DEALER b on a.dealer_id=b.dealer_id \n");
		sqlStr.append(" left outer join TM_VEHICLE v on v.vin=a.vin \n");
		sqlStr.append(" left outer join vw_material_group vw on v.package_id=vw.package_id \n");
		sqlStr.append(" left outer join TT_AS_ACTIVITY AA ON AA.ACTIVITY_CODE=A.CAMPAIGN_CODE \n");
		sqlStr.append(" left outer join TT_AS_REPAIR_ORDER RR ON RR.RO_NO=A.RO_NO \n");
		sqlStr.append(" left outer join TT_AS_WR_FOREAPPROVAL FP ON RR.RO_NO=FP.RO_NO AND REPORT_STATUS="+Constant.RO_FORE_02+" \n");
		sqlStr.append(" left outer join TC_USER TS ON FP.AUDIT_PERSON=TS.USER_ID \n");
		sqlStr.append(" left outer join TT_DEALER_ACTUAL_SALES DA ON DA.VEHICLE_ID=V.VEHICLE_ID \n");
		sqlStr.append(" left outer join tt_customer tc on tc.ctm_id=da.ctm_id \n");
		//sqlStr.append(" left outer join tt_as_wr_appauthitem ap on a.id=ap.id and ap.approval_person like '%授权审核%'\n");
		sqlStr.append(" where 1=1 ");
		sqlStr.append(" AND a.ID='" + id + "' ");
		TtAsWrApplicationExtPO tawep = new TtAsWrApplicationExtPO();
		List<TtAsWrApplicationExtPO> ls = select(TtAsWrApplicationExtPO.class,sqlStr.toString(), null);
		if (ls != null) {
			if (ls.size() > 0) {
				tawep = ls.get(0);
			}
		}
		return tawep;
	}
	
	/**
	 * 
	* @Title: queryPartLabour 
	* @Description: TODO(查询配件和工时在一个列表中) 
	* @param @param id
	* @param @return    设定文件 
	* @return List    返回类型 
	* @throws
	 */
	public List<TtAsWrPartsitemExtPO> queryPartLabour(String id) {
		StringBuffer sql= new StringBuffer();
		sql.append("select p.part_Id,\n");
		sql.append("       p.part_code as part_code,\n");  
		sql.append("       p.part_name as part_name,\n");  
		sql.append("       p.amount as amount,\n");  
		sql.append("       p.quantity as quantity,\n");  
		sql.append("       p.remark as remark,\n");  
		sql.append("       p.down_part_code as down_part_code,\n");  
		sql.append("       p.down_part_name as down_part_name,\n");  
		sql.append("       l.wr_labourcode as wr_labourcode,\n");  
		sql.append("       l.wr_labourname as wr_labourname,\n");  
		sql.append("       l.labour_hours as labour_hours,\n");  
		sql.append("       l.labour_amount as labour_amount,\n");  
		sql.append("       l.first_part as first_part,l.trouble_type\n");  
		sql.append("  from tt_as_wr_partsitem p\n");
		sql.append("left outer join tt_as_wr_labouritem l on p.wr_labourcode=l.wr_labourcode and p.id=l.id\n" );
		sql.append("where 1=1\n" );
		sql.append("and p.id="+id+"");
		List<TtAsWrPartsitemExtPO> ls = select(TtAsWrPartsitemExtPO.class,sql.toString(), null);
		return ls;
	}
	/**
	 * 
	 * @Title: queryApplicationDetailById
	 * @Description: TODO(明细查询)
	 * @param
	 * @param id
	 * @param
	 * @return 设定文件
	 * @return TtAsWrApplicationExtPO 返回类型
	 * @throws
	 */
	public TtAsWrApplicationExtPO queryApplicationDetailById(String id) {
		StringBuffer sqlStr = new StringBuffer();
		sqlStr
				.append(" select a.*,(select brand_name from VW_MATERIAL_GROUP vw where vw.package_id=v.package_id) as brand_name,g1.group_name as series_name,g2.group_name as model_name,b.dealer_code as dealer_code, b.dealer_name as dealer_name, \n");
		sqlStr
				.append(" v.ENGINE_NO as engine_no,v.GEARBOX_NO as gearbox_no,v.REARAXLE_NO as rearaxle_no,v.TRANSFER_NO as transfer_no,v.LICENSE_NO as license_no, \n");
		sqlStr
				.append(" b1.code_name as damage_degree_name,b2.code_name as damage_area_name,b3.code_name as damage_type_name,b4.code_name as trouble_name, \n");
		sqlStr.append(" c.code_desc as claim_name ");
		sqlStr.append(" from TT_AS_WR_APPLICATION a   \n");
		sqlStr
				.append(" left outer join TM_DEALER b on a.dealer_id=b.dealer_id \n");
		sqlStr.append(" left outer join TM_VEHICLE v on v.vin=a.vin \n");
		sqlStr
				.append(" left outer join TM_VHCL_MATERIAL_GROUP g1 on v.series_id=g1.group_id \n");
		sqlStr
				.append(" left outer join TM_VHCL_MATERIAL_GROUP g2 on v.model_id=g2.group_id \n");
		sqlStr
				.append(" left outer join tm_business_chng_code b1 on b1.type_code='"
						+ Constant.BUSINESS_CHNG_CODE_01
						+ "' and b1.code=a.damage_degree \n");
		sqlStr
				.append(" left outer join tm_business_chng_code b2 on b2.type_code='"
						+ Constant.BUSINESS_CHNG_CODE_02
						+ "' and b2.code=a.damage_area \n");
		sqlStr
				.append(" left outer join tm_business_chng_code b3 on b3.type_code='"
						+ Constant.BUSINESS_CHNG_CODE_03
						+ "' and b3.code=a.damage_type \n");
		sqlStr
				.append(" left outer join tm_business_chng_code b4 on b4.type_code='"
						+ Constant.BUSINESS_CHNG_CODE_04
						+ "' and b4.code=a.trouble_code \n");
		sqlStr
				.append(" left outer join tc_code c on c.code_id=a.claim_type \n");
		sqlStr.append(" where 1=1 ");
		sqlStr.append(" AND a.ID='" + id + "' ");
		TtAsWrApplicationExtPO tawep = new TtAsWrApplicationExtPO();
		List<TtAsWrApplicationExtPO> ls = select(TtAsWrApplicationExtPO.class,
				sqlStr.toString(), null);
		if (ls != null) {
			if (ls.size() > 0) {
				tawep = ls.get(0);
			}
		}
		return tawep;
	}

	/**
	 * 
	 * @Title: queryItemById
	 * @Description: TODO(查询工时通过索赔单ID)
	 * @param
	 * @param id
	 * @param
	 * @return 设定文件
	 * @return List 返回类型
	 * @throws
	 */
	public List<ClaimListBean> queryItemById(String id) {
	    StringBuffer sql = new StringBuffer();
	    List<ClaimListBean> allLs = new LinkedList<ClaimListBean>();
	    String bmlCode = "";
	    // Map map = new HashMap();
	    // 取主工时
	    sql.append("select l.labour_id,\n");
	    sql.append("       l.id,\n");  
	    sql.append("       l.wr_labourcode,\n");  
	    sql.append("       l.wr_labourname,\n");  
	    sql.append("       l.labour_code,\n");  
	    sql.append("       l.labour_name,\n");  
	    sql.append("       l.labour_hours,\n");  
	    sql.append("       l.labour_quantity,\n");  
	    sql.append("       l.labour_price,\n");  
	    sql.append("       l.labour_amount,\n");  
	    sql.append("       l.is_mainlabour,\n");  
	    sql.append("       l.auth_code,\n");  
	    sql.append("       l.create_date,\n");  
	    sql.append("       l.create_by,\n");  
	    sql.append("       l.debit_labour_id,\n");  
	    sql.append("       l.balance_amount,\n");  
	    sql.append("       l.deduct_amount,\n");  
	    sql.append("       l.b_ml_code,\n");  
	    sql.append("       l.trouble_code,\n");  
	    sql.append("       l.damage_type,\n");  
	    sql.append("       l.damage_area,\n");  
	    sql.append("       l.damage_degree,\n");  
	    sql.append("       l.labour_count,\n");  
	    sql.append("       l.is_agree,\n");  
	    sql.append("       l.trouble_code_name,\n");  
	    sql.append("       l.damage_type_name,\n");  
	    sql.append("       l.damage_area_name,\n");  
	    sql.append("       l.damage_degree_name,\n");  
	    sql.append("       l.pay_type,\n");  
	    sql.append("       l.balance_quantity,\n");  
	    sql.append("       l.first_part,\n");  
	    sql.append("       l.auth_remark,\n");  
	    sql.append("       l.is_claim,\n");  
	    sql.append("       l.labour_quantity_hidden,\n");  
	    sql.append("       l.balance_price,\n");  
	    sql.append("       l.apply_quantity,\n");  
	    sql.append("       l.apply_price,\n");  
	    sql.append("       l.apply_amount,l.trouble_type,\n");  
	    sql.append("       (select count(1) from tt_as_wr_authmonitorlab ll where ll.labour_operation_no = l.wr_labourcode) as is_fore\n");  
	    sql.append("  from TT_AS_WR_LABOURITEM l\n");
	    sql.append(" WHERE l.ID='" + id + "'");
	    sql.append(" AND l.IS_MAINLABOUR = "+Constant.IS_MAIN_TROUBLE);
	    sql.append(" order by l.first_part");
	    List<TtAsWrLabouritemPO> ls = select(TtAsWrLabouritemPO.class, sql
	        .toString(), null);
	    if (ls == null) {

	    } else {
	      if (ls.size() == 0) {
	        // return null;
	      } else {
	        for (int i = 0; i < ls.size(); i++) {

	          ClaimListBean clb = new ClaimListBean();
	          TtAsWrLabouritemPO tawp = new TtAsWrLabouritemPO();
	          List<Object> lsTm = new ArrayList<Object>();
	          tawp = ls.get(i);
	          bmlCode = tawp.getWrLabourcode();
	          clb.setMain(tawp);
	          // allLs.add(tawp);
	          // 取附属工时
	          StringBuffer sql1 = new StringBuffer();
	          sql1.append("select l.labour_id,\n");
	          sql1.append("       l.id,\n");  
	          sql1.append("       l.wr_labourcode,\n");  
	          sql1.append("       l.wr_labourname,\n");  
	          sql1.append("       l.labour_code,\n");  
	          sql1.append("       l.labour_name,\n");  
	          sql1.append("       l.labour_hours,\n");  
	          sql1.append("       l.labour_quantity,\n");  
	          sql1.append("       l.labour_price,\n");  
	          sql1.append("       l.labour_amount,\n");  
	          sql1.append("       l.is_mainlabour,\n");  
	          sql1.append("       l.auth_code,\n");  
	          sql1.append("       l.create_date,\n");  
	          sql1.append("       l.create_by,\n");  
	          sql1.append("       l.debit_labour_id,\n");  
	          sql1.append("       l.balance_amount,\n");  
	          sql1.append("       l.deduct_amount,\n");  
	          sql1.append("       l.b_ml_code,\n");  
	          sql1.append("       l.trouble_code,\n");  
	          sql1.append("       l.damage_type,\n");  
	          sql1.append("       l.damage_area,\n");  
	          sql1.append("       l.damage_degree,\n");  
	          sql1.append("       l.labour_count,\n");  
	          sql1.append("       l.is_agree,\n");  
	          sql1.append("       l.trouble_code_name,\n");  
	          sql1.append("       l.damage_type_name,\n");  
	          sql1.append("       l.damage_area_name,\n");  
	          sql1.append("       l.damage_degree_name,\n");  
	          sql1.append("       l.pay_type,\n");  
	          sql1.append("       l.balance_quantity,\n");  
	          sql1.append("       l.first_part,\n");  
	          sql1.append("       l.auth_remark,\n");  
	          sql1.append("       l.is_claim,\n");  
	          sql1.append("       l.labour_quantity_hidden,\n");  
	          sql1.append("       l.balance_price,\n");  
	          sql1.append("       l.apply_quantity,\n");  
	          sql1.append("       l.apply_price,\n");  
	          sql1.append("       l.apply_amount,l.trouble_type,\n");  
	          sql1.append("       (select count(1) from tt_as_wr_authmonitorlab ll where ll.labour_operation_no = l.wr_labourcode) as is_fore\n");  
	          sql1.append("  from TT_AS_WR_LABOURITEM l\n");
	          sql1.append(" WHERE 1=1 ");
	          sql1.append(" AND l.ID='" + id + "'");
	          sql1.append(" and l.IS_MAINLABOUR = "+Constant.IS_NOT_MAIN_TROUBLE);
	          sql1.append(" and l.B_ML_CODE='" + bmlCode + "'");
	          sql1.append(" order by l.first_part");
	          List<TtAsWrLabouritemPO> ls1 = select(TtAsWrLabouritemPO.class, sql1.toString(), null);
	          if (ls1 == null) {
	            // return allLs;
	          } else {
	            for (int j = 0; j < ls1.size(); j++) {
	              lsTm.add(ls1.get(j));
	            }
	            clb.setAdditional(lsTm);
	          }
	          allLs.add(clb);
	        }
	      }
	    }

	    // 返回合并工时
	    return allLs;
  }

	/**
	 * 
	 * @Title: queryPartById
	 * @Description: TODO(查询配件通过索赔单ID)
	 * @param
	 * @param id
	 * @param
	 * @return 设定文件
	 * @return List 返回类型
	 * @throws
	 */
	public List<ClaimListBean> queryPartById(String id) {
		StringBuffer sql = new StringBuffer();
		//轿微区分
		TcCodePO code = new TcCodePO() ;
		code.setType("8008") ;
		List list = dao.select(code) ;
		if(list.size()>0)
			code = (TcCodePO)list.get(0);
		
		List<TtAsWrApplicationPO> lsTawp = new ArrayList<TtAsWrApplicationPO>();
		// List<TtAsWrPartsitemPO> allLs = new LinkedList<TtAsWrPartsitemPO>();
		List<TtAsWrPartsitemPO> ls = new ArrayList<TtAsWrPartsitemPO>();
		List<TtAsWrPartsitemPO> ls1 = new ArrayList<TtAsWrPartsitemPO>();
		List<ClaimListBean> allLs = new LinkedList<ClaimListBean>();
		// Map map = new HashMap();
		/* TtAsWrApplicationPO tawp = new TtAsWrApplicationPO(); */
		try {
			/*
			 * tawp.setId(Utility.getLong(id)); lsTawp = select(tawp);
			 */
			/*
			 * if (lsTawp!=null) { if (lsTawp.size()>0) {
			 */
			/*
			 * tawp = lsTawp.get(0); String failPart = tawp.getFailedPartNo();
			 */
			// 取主配件
			sql.append("select a.part_id,\n");
			sql.append("       a.id,\n");  
			sql.append("       a.part_code,\n");  
			sql.append("       a.part_name,\n");  
			sql.append("       a.down_part_code,\n");  
			sql.append("       a.down_part_name,\n");  
			sql.append("       a.quantity,\n");  
			sql.append("       a.price,\n");  
			sql.append("       a.amount,\n");  
			sql.append("       a.producer_code,\n");  
			sql.append("       a.producer_name,\n");  
			sql.append("       a.is_mainpart,\n");  
			sql.append("       a.remark,\n");  
			sql.append("       a.auth_code,\n");  
			sql.append("       a.create_date,\n");  
			sql.append("       a.create_by,\n");  
			sql.append("       a.debit_part_id,\n");  
			sql.append("       a.return_num,\n");  
			sql.append("       a.old_part_code,\n");  
			sql.append("       a.balance_amount,\n");  
			sql.append("       a.deduct_amount,\n");  
			sql.append("       a.b_mp_code,\n");  
			sql.append("       a.wr_labourcode,\n");  
			sql.append("       a.down_product_code,\n");  
			sql.append("       a.down_product_name,\n");  
			sql.append("       nvl(a.is_gua,0) as is_gua,\n");  
			sql.append("       a.is_agree,\n");  
			sql.append("       a.pay_type,\n");   
			sql.append("       a.balance_quantity,\n");  
			sql.append("       a.auth_remark,\n");  
			sql.append("       a.is_claim,\n");  
			sql.append("       a.balance_price,\n");  
			sql.append("       a.apply_quantity,\n");  
			sql.append("       a.apply_price,\n");  
			sql.append("       a.apply_amount,\n");  
			if("80081001".equals(code.getCodeId())){
				sql.append("       (select count(1) from tt_as_wr_authmonitorpart p where p.part_code = a.part_code and is_del = 0) as is_fore\n");  
			}else
				sql.append("       (SELECT COUNT(1) FROM TM_PT_PART_TYPE T, TM_PT_PART_BASE B WHERE T.IS_MAX = 1 AND a.PART_ID = B.PART_ID AND B.PART_TYPE = T.ID) as is_fore\n");  
			sql.append(" from tt_as_wr_partsitem a\n");
			sql.append(" WHERE 1=1 ");
			sql.append(" AND A.ID='" + id + "'");
			sql.append(" AND A.B_MP_CODE IS NULL ");
			sql.append(" order by a.part_id desc ");
			ls = select(TtAsWrPartsitemPO.class, sql.toString(), null);
			if (ls == null) {

			} else {
				if (ls.size() > 0) {
					TtAsWrPartsitemPO t1 = new TtAsWrPartsitemPO();
					for (int i = 0; i < ls.size(); i++) {
						StringBuffer sql1 = new StringBuffer();
						List<Object> lsTm = new ArrayList<Object>();
						ClaimListBean clb = new ClaimListBean();
						clb.setMain(ls.get(i));
						// allLs.add(ls.get(i));
						// 取附属配件
						/*sql1.append(" SELECT A.* FROM TT_AS_WR_PARTSITEM A ");
						sql1.append(" WHERE 1=1 ");*/
						sql1.append("select a.part_id,\n");
						sql1.append("       a.id,\n");  
						sql1.append("       a.part_code,\n");  
						sql1.append("       a.part_name,\n");  
						sql1.append("       a.down_part_code,\n");  
						sql1.append("       a.down_part_name,\n");  
						sql1.append("       a.quantity,\n");  
						sql1.append("       a.price,\n");  
						sql1.append("       a.amount,\n");  
						sql1.append("       a.producer_code,\n");  
						sql1.append("       a.producer_name,\n");  
						sql1.append("       a.is_mainpart,\n");  
						sql1.append("       a.remark,\n");  
						sql1.append("       a.auth_code,\n");  
						sql1.append("       a.create_date,\n");  
						sql1.append("       a.create_by,\n");  
						sql1.append("       a.debit_part_id,\n");  
						sql1.append("       a.return_num,\n");  
						sql1.append("       a.old_part_code,\n");  
						sql1.append("       a.balance_amount,\n");  
						sql1.append("       a.deduct_amount,\n");  
						sql1.append("       a.b_mp_code,\n");  
						sql1.append("       a.wr_labourcode,\n");  
						sql1.append("       a.down_product_code,\n");  
						sql1.append("       a.down_product_name,\n");  
						sql1.append("       nvl(a.is_gua,0) is_gua,\n");  
						sql1.append("       a.is_agree,\n");  
						sql1.append("       a.pay_type,\n");  
						sql1.append("       a.balance_quantity,\n");  
						sql1.append("       a.auth_remark,\n");  
						sql1.append("       a.is_claim,\n");  
						sql1.append("       a.balance_price,\n");  
						sql1.append("       a.apply_quantity,\n");  
						sql1.append("       a.apply_price,\n");  
						sql1.append("       a.apply_amount,\n");  
						//添加最后一句的目的是为了在页面上显示是否为监控配件！
						if("80081001".equals(code.getCodeId())){
							sql1.append("       (select count(1) from tt_as_wr_authmonitorpart p where p.part_code = a.part_code and is_del = 0) as is_fore\n");  
						}else
							sql1.append("       (SELECT COUNT(1) FROM TM_PT_PART_TYPE T, TM_PT_PART_BASE B WHERE T.IS_MAX = 1 AND a.PART_ID = B.PART_ID AND B.PART_TYPE = T.ID) as is_fore\n");  
						sql1.append(" from tt_as_wr_partsitem a\n");
						sql1.append(" where 1=1\n");
						sql1.append(" AND A.ID='" + id + "'");
						sql1.append(" AND A.B_MP_CODE = '"
								+ ls.get(i).getPartCode() + "'");
						sql.append(" order by a.part_id desc ");
						ls1 = select(TtAsWrPartsitemPO.class, sql1.toString(),null);
						if (ls1 != null) {
							if (ls1.size() > 0) {
								for (int j = 0; j < ls1.size(); j++) {
									lsTm.add(ls1.get(j));
								}
								clb.setAdditional(lsTm);
							}
						}
						allLs.add(clb);
					}
				}
			}
			// t1 = ls.get(0);
			// allLs.add(t1);
			/*
			 * //取附属配件 sql1.append(" SELECT A.* FROM TT_AS_WR_PARTSITEM A ");
			 * sql1.append(" WHERE 1=1 "); sql1.append(" AND A.ID='"+id+"'");
			 * sql1.append(" AND A.B_MP_CODE = '"+failPart.trim()+"'"); ls1 =
			 * select (TtAsWrPartsitemPO.class,sql1.toString(),null); if
			 * (ls1!=null) { if (ls1.size()>0) { for (int i = 0;i<ls1.size();i++) {
			 * allLs.add(ls1.get(i)); } } }
			 */
			/*
			 * }else { //return null; }
			 */

			/* } */
			/*
			 * }else { //return null; } }else { //return null; }
			 */
		} catch (Exception e) {
			e.printStackTrace();
		}

		return allLs;
	}
	
	//查询三包单据审核界面要显示零件的换件次数
	public int getCount(String id,String partName){
		String sql = "select count(*) as count from Tt_As_Wr_Partsitem p where p.id in (select id from tt_as_wr_application where vin = (select vin from tt_as_wr_application where id = '"+id+"') and status not in ('"+Constant.CLAIM_APPLY_ORD_TYPE_05+"', '"+Constant.CLAIM_APPLY_ORD_TYPE_06+"')) and p.part_code='"+partName+"' and p.part_name not like '%无零件%'";
		List<Map<String, Object>> list = this.pageQuery(sql, null, this.getFunName());
		return ((BigDecimal)list.get(0).get("COUNT")).intValue();
	}

	/**
	 * 
	 * @Title: queryOtherByid
	 * @Description: TODO(查询其他项目通过ID)
	 * @param
	 * @param id
	 * @param
	 * @return 设定文件
	 * @return List 返回类型
	 * @throws
	 */
	public List<TtAsWrNetitemExtPO> queryOtherByid(String id) {
		StringBuffer sql = new StringBuffer();
		List<TtAsWrNetitemExtPO> ls = new ArrayList<TtAsWrNetitemExtPO>();
		sql.append(" SELECT A.*,B.FEE_NAME as fee_name ");
		sql.append(" FROM TT_AS_WR_NETITEM A ");
		sql
				.append(" LEFT OUTER JOIN TT_AS_WR_OTHERFEE B ON B.FEE_CODE = A.ITEM_CODE ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND A.ID='" + id + "'");
		ls = select(TtAsWrNetitemExtPO.class, sql.toString(), null);
		return ls;
	}

	/**
	 * 
	 * @Title: queryAttById
	 * @Description: TODO(通过ID查询附件)
	 * @param
	 * @param id
	 * @param
	 * @return 设定文件
	 * @return List<FsFileuploadPO> 返回类型
	 * @throws
	 */
	public List<FsFileuploadPO> queryAttById(String id) {
		StringBuffer sql = new StringBuffer();
		List<FsFileuploadPO> ls = new ArrayList<FsFileuploadPO>();
		sql.append(" SELECT A.* FROM FS_FILEUPLOAD A ");
		// sql.append(" LEFT OUTER JOIN FS_FILEUPLOAD B ON B.YWZJ = A.ID ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND A.YWZJ='" + id + "'");
		ls = select(FsFileuploadPO.class, sql.toString(), null);
		return ls;
	}

	/**
	 * 
	 * @Title: queryActivity
	 * @Description: TODO(查询服务活动的费用)
	 * @param
	 * @param id
	 * @param
	 * @return 设定文件
	 * @return List<TtAsActivityPO> 返回类型 
	 * @throws
	 */
	public List<TtAsActivityPO> queryActivity(Long id) {
		StringBuffer sql = new StringBuffer();
		List<TtAsActivityPO> ls = new ArrayList<TtAsActivityPO>();
		sql.append(" SELECT A.* FROM  TT_AS_WR_APPLICATION W ");
		sql
				.append(" LEFT OUTER JOIN TT_AS_ACTIVITY A ON W.CAMPAIGN_CODE = A.ACTIVITY_CODE ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND W.ID='" + id + "'");
		ls = select(TtAsActivityPO.class, sql.toString(), null);
		return ls;
	}

	/**
	 * 
	 * @Title: feeQuery
	 * @Description: TODO(查询免费保养费用)
	 * @param
	 * @param pageSize
	 * @param
	 * @param curPage
	 * @param
	 * @param whereSql
	 * @param
	 * @param params
	 * @param
	 * @param type
	 * @param
	 * @param flagStr
	 * @param
	 * @return
	 * @param
	 * @throws Exception
	 *             设定文件
	 * @return PageResult<Map<String,Object>> 返回类型
	 * @throws
	 */
	public PageResult<Map<String, Object>> feeQuery(int pageSize, int curPage,
			String whereSql, List<Object> params, String type, String flagStr)
			throws Exception {
		PageResult<Map<String, Object>> result = null;
		List list = dao.getFeeType(type);
		StringBuffer sb = new StringBuffer();
		sb.append(" select t.group_id,t.group_code,t.group_name ");
		for (int i = 0; i < list.size(); i++) {
			HashMap tcpo = (HashMap) list.get(i);
			sb.append(" ,max(decode(t.fee_type," + tcpo.get("CODE_ID")
					+ ",t.fee)) as \"" + tcpo.get("CODE_ID") + "\" ");
		}
		sb.append(" from ");
		sb
				.append(" (select tvmg.group_id,tvmg.group_code,tvmg.group_name,tawmf.fee_id,tawmf.fee,tawmf.fee_type ");
		sb.append(" from TM_VHCL_MATERIAL_GROUP tvmg ");
		sb
				.append(" left outer join TT_AS_WR_MODEL_FEE tawmf on tvmg.group_id = tawmf.model_id ");
		sb.append(" where tvmg.group_level = 3 ");
		sb.append(" and tvmg.group_id ");
		sb.append(flagStr);
		sb
				.append(" ( select a.model_id from TT_AS_WR_MODEL_FEE a group by a.model_id ) ");
		if (whereSql != null && !"".equals(whereSql.trim())) {
			sb.append(whereSql);
		}
		sb.append(" ) t ");
		sb.append(" group by t.group_id,t.group_code,t.group_name ");
		sb.append(" order by t.group_id ");
		result = (PageResult<Map<String, Object>>) pageQuery(sb.toString(),
				params, getFunName(), pageSize, curPage);
		return result;
	}

	/**
	 * 
	 * @Title: getFeeType
	 * @Description: TODO(获得保养费用的tc_code列表)
	 * @param
	 * @param type
	 * @param
	 * @return
	 * @return List
	 * @throws
	 */
	@SuppressWarnings("unchecked")
	public List getFeeType(String type) {
		List<Object> params = new LinkedList<Object>();
		StringBuilder sb = new StringBuilder();
		sb.append(" select tc.code_id,tc.code_desc,tc.type from tc_code tc ");
		sb.append(" where 1=1 ");
		if (Utility.testString(type)) {
			sb.append(" and tc.type = ? ");
			params.add(type);
		}
		sb.append(" and tc.status = 10011001");
		sb.append(" order by tc.num");
		List<Map<String, Object>> list = pageQuery(sb.toString(), params,
				getFunName());
		return list;
	}

	public void insertTtAsWrLabouritem(TtAsWrLabouritemPO t) {
		insert(t);
	}

	/**
	 * 查询对应索赔申请单授权信息
	 * 
	 * @param claimId
	 *            索赔申请单ID
	 * @return List<TtAsWrAppauthitemPO>
	 */
	public List<TtAsWrAppauthitemPO> queryAppAuthInfo(String claimId) {
		List<Object> condition = new ArrayList<Object>();
		condition.add(Long.parseLong(claimId));

		StringBuffer sql = new StringBuffer();
		sql
				.append("SELECT A.ID,A.APPROVAL_PERSON,A.APPROVAL_LEVEL_CODE,A.APPROVAL_DATE,\n");
		sql
				.append("    B.CODE_DESC APPROVAL_RESULT,A.AUTHORIZED_CODE,A.REMARK,A.CREATE_BY,\n");
		sql.append("    A.CREATE_DATE,A.UPDATE_BY,A.UPDATE_DATE\n");
		sql.append("    FROM TT_AS_WR_APPAUTHITEM A,TC_CODE B\n");
		sql.append("    WHERE 1=1\n");
		sql.append("    AND A.APPROVAL_RESULT = B.CODE_ID(+)\n");
		sql.append("    AND A.ID = ?\n");
		sql.append("    ORDER BY A.CREATE_DATE DESC");

		List<TtAsWrAppauthitemPO> resList = this.select(
				TtAsWrAppauthitemPO.class, sql.toString(), condition);

		return resList;
	}

	/**
	 * 查询对应索赔申请单需要审核的原因
	 * 
	 * @param claimId
	 *            索赔申请单ID
	 * @return TtAsWrWrauthorizationPO
	 */
	public TtAsWrWrauthorizationPO queryAuthReason(String claimId) {

		String sql = "SELECT * FROM TT_AS_WR_WRAUTHORIZATION "
				+ " WHERE ID = ? " + " AND ROWNUM < 2";

		List<Object> paraList = new ArrayList<Object>();
		paraList.add(claimId);

		TtAsWrWrauthorizationPO resultPO = null;

		List<TtAsWrWrauthorizationPO> resultList = this.select(
				TtAsWrWrauthorizationPO.class, sql, paraList);
		if (resultList != null && resultList.size() > 0) {
			resultPO = resultList.get(0);
		}
		return resultPO;
	}

	/**
	 * 
	 * @Title: queryBrand
	 * @Description: TODO(取品牌)
	 * @param
	 * @return 设定文件
	 * @return List<VwMaterialGroupPO> 返回类型
	 * @throws
	 */
	public List<VwMaterialGroupPO> queryBrand() {

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT distinct brand_id,brand_name,brand_code \n");
		sql.append("    from vw_material_group t\n");
		sql.append(" where 1=1 ");
		List<VwMaterialGroupPO> resList = this.select(VwMaterialGroupPO.class,
				sql.toString(), null);

		return resList;
	}

	/**
	 * 
	 * @Title: querySeries
	 * @Description: TODO(取车系)
	 * @param
	 * @return 设定文件
	 * @return List<VwMaterialGroupPO> 返回类型
	 * @throws
	 */
	public List<VwMaterialGroupPO> querySeries(String brand) {

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT distinct series_id,series_name,series_code \n");
		sql.append("    from vw_material_group t\n");
		sql.append(" where 1=1 ");
		if (Utility.testString(brand)) {
			sql.append(" and brand_code='" + brand + "' ");
		}
		List<VwMaterialGroupPO> resList = this.select(VwMaterialGroupPO.class,
				sql.toString(), null);

		return resList;
	}

	/**
	 * 
	 * @Title: queryModel
	 * @Description: TODO(取车型)
	 * @param
	 * @return 设定文件
	 * @return List<VwMaterialGroupPO> 返回类型
	 * @throws
	 */
	public List<VwMaterialGroupPO> queryModel(String series) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT distinct model_id,model_name,model_code \n");
		sql.append("    from vw_material_group t\n");
		sql.append(" where 1=1 ");
		if (Utility.testString(series)) {
			sql.append(" and series_code='" + series + "'");
		}
		List<VwMaterialGroupPO> resList = this.select(VwMaterialGroupPO.class,
				sql.toString(), null);

		return resList;
	}

	/**
	 * 
	 * @Title: queryGame
	 * @Description: TODO(查询免费保养次数下拉框)
	 * @param
	 * @param modelId
	 * @param
	 * @return 设定文件
	 * @return List<TtAsWrGamefeePO> 返回类型 
	 * @throws
	 */
	public List<TtAsWrGamefeePO> queryGame(Long modelId) {
		StringBuffer sql = new StringBuffer();
		sql
				.append("select distinct(MAINTAINFEE_ORDER),MANINTAIN_FEE from TT_AS_WR_GAMEFEE t left outer join  TT_AS_WR_GAMEMODEL m on t.game_id=m.game_id\n");
		sql.append("     where 1=1\n");
		if (modelId != null) {
			sql.append("  and m.model_id=" + modelId + "");
		}
		sql.append(" order by maintainfee_order asc ");
		List<TtAsWrGamefeePO> resList = this.select(TtAsWrGamefeePO.class, sql
				.toString(), null);
		return resList;

	}

	/**
	 * 
	 * @Title: queryActivityCombo
	 * @Description: TODO(服务活动下拉框)
	 * @param
	 * @param modelId
	 * @param
	 * @return 设定文件
	 * @return List<TtAsActivityPO> 返回类型 
	 * @throws
	 */
	public List<TtAsActivityPO> queryActivityCombo() {
		StringBuffer sql = new StringBuffer();
		sql.append("	SELECT A.*  FROM TT_AS_ACTIVITY A  \n");
		sql.append("    WHERE A.STATUS in( "
				+ Constant.SERVICEACTIVITY_STATUS_02 + ") AND A.IS_DEL="
				+ Constant.IS_DEL_00 + " \n");// 服务活动管理--服务活动活动状态:[10681001:尚未发布;10681002:已经发布;10681003:重新发布]
		// sql.append(" AND A.ACTIVITY_CODE NOT IN (SELECT CAMPAIGN_CODE FROM
		// WHERE ) ");
		sql.append("    ORDER BY trim(A.ACTIVITY_CODE) desc  \n");

		List<TtAsActivityPO> resList = this.select(TtAsActivityPO.class, sql
				.toString(), null);
		return resList;
	}
	/**
	 * 
	* @Title: queryActivityCombo 
	* @Description: TODO(根据车型查询对应服务活动) 
	* @param @param modelId
	* @param @return    设定文件 
	* @return List<TtAsActivityPO>    返回类型 
	* @throws
	 */
	public List<TtAsActivityPO> queryActivityCombo(String modelId) {
		StringBuffer sql = new StringBuffer();
		sql.append("	SELECT A.*  FROM TT_AS_ACTIVITY A  \n");
		sql.append("LEFT OUTER JOIN TT_AS_ACTIVITY_MGROUP M ON A.activity_id=M.activity_id \n");
		sql.append("    WHERE A.STATUS in( "
				+ Constant.SERVICEACTIVITY_STATUS_02 + ") AND A.IS_DEL="
				+ Constant.IS_DEL_00 + " \n");// 服务活动管理--服务活动活动状态:[10681001:尚未发布;10681002:已经发布;10681003:重新发布]
		// sql.append(" AND A.ACTIVITY_CODE NOT IN (SELECT CAMPAIGN_CODE FROM
		// WHERE ) ");
		if (Utility.testString(modelId)) {
			sql.append(" and  MATERIAL_GROUP_ID="+modelId+"");
		}
		sql.append("    ORDER BY trim(A.ACTIVITY_CODE) desc  \n");

		List<TtAsActivityPO> resList = this.select(TtAsActivityPO.class, sql
				.toString(), null);
		return resList;
	}
	public PageResult<TtAsActivityPO> queryActivityComboByVin(Map<String,String> map,int curPage,int pageSize) {
		StringBuffer sql = new StringBuffer();
		sql.append("	SELECT A.*  FROM TT_AS_ACTIVITY A  \n");
		sql.append("WHERE 1=1 \n");
		sql.append(" and a.is_del=0 and a.status="+Constant.SERVICEACTIVITY_STATUS_02+" \n");
		sql.append("and (((select count(*) from tt_as_repair_ORDER R  where R.CAM_CODE=a.activity_code AND VIN='"+map.get("vin")+"')<A.SINGLE_CAR_NUM) OR A.SINGLE_CAR_NUM IS NULL)\n" );
		sql.append("AND ((SELECT COUNT(*) FROM tt_as_repair_ORDER R  where R.CAM_CODE=a.activity_code AND R.DEALER_CODE='"+map.get("dealerCode")+"')<A.MAX_CAR OR A.MAX_CAR IS NULL)");
		//sql.append("LEFT OUTER JOIN TT_AS_ACTIVITY_DEALER B ON A.ACTIVITY_ID=B.ACTIVITY_ID\n" );
		//sql.append("LEFT OUTER JOIN TT_AS_ACTIVITY_VEHICLE C ON A.ACTIVITY_ID=C.ACTIVITY_ID\n" );
		//sql.append("LEFT OUTER JOIN TT_AS_ACTIVITY_AGE E ON A.ACTIVITY_ID=E.ACTIVITY_ID AND E.DATe_TYPE="+Constant.SERVICEACTIVITY_DATE_TYPE_01+"\n" );
		//sql.append("LEFT OUTER JOIN TT_AS_ACTIVITY_AGE F ON A.ACTIVITY_ID=F.ACTIVITY_ID AND F.DATe_TYPE="+Constant.SERVICEACTIVITY_DATE_TYPE_02+"\n" );
		//sql.append("LEFT OUTER JOIN tt_as_activity_yieldly G ON A.ACTIVITY_ID=G.ACTIVITY_ID \n");
		//sql.append("LEFT OUTER JOIN TT_AS_ACTIVITY_MGROUP M ON A.activity_id=M.activity_id \n");
		//sql.append("    WHERE A.STATUS in( "
		//		+ Constant.SERVICEACTIVITY_STATUS_02 + ") AND A.IS_DEL="
		//		+ Constant.IS_DEL_00 + " \n");// 服务活动管理--服务活动活动状态:[10681001:尚未发布;10681002:已经发布;10681003:重新发布]
		// sql.append(" AND A.ACTIVITY_CODE NOT IN (SELECT CAMPAIGN_CODE FROM
		// WHERE ) ");
		List params = new LinkedList();
		params.add(map.get("vin"));
		params.add(map.get("dealerCode"));
		params.add(map.get("inMileage"));
		
		String ss = callFunction("f_get_activity_id", java.sql.Types.VARCHAR,params )==null?"-1":callFunction("f_get_activity_id", java.sql.Types.VARCHAR,params ).toString();
		if (Utility.testString(map.get("vin"))) {
			sql.append(" and a.activity_id in ("+ss+")");
		}
		/*if (Utility.testString(map.get("modelId"))) {
			sql.append(" and  MATERIAL_GROUP_ID="+map.get("modelId")+"  \n");
		}
		sql.append(" and G.CAR_YIELDLY='"+map.get("yieldly")+"'");
		if (Utility.testString(map.get("dealerId"))) {
			sql.append(" and  B.DEALER_ID="+map.get("dealerId")+"  \n");
		}*/
		/*if (Utility.testString(map.get("proDate"))) {
			sql.append(" and  SALE_DATE_START<=to_date('"
							+ map.get("proDate")
							+ " 23:59:59', 'yyyy-mm-dd hh24:mi:ss') \n");
			sql.append(" and  SALE_DATE_END>=to_date('"
					+ map.get("proDate")
					+ "', 'yyyy-mm-dd hh24:mi:ss') \n");
		}
		if (Utility.testString(map.get("vin"))) {
			sql.append(" and  C.VIN='"+map.get("vin")+"'  \n");
		}
		if (Utility.testString(map.get("purDate"))) {
			sql.append(" and  SALE_DATE_START<=to_date('"
							+ map.get("purDate")
							+ " 23:59:59', 'yyyy-mm-dd hh24:mi:ss') \n");
			sql.append(" and  SALE_DATE_END>=to_date('"
					+ map.get("purDate")
					+ "', 'yyyy-mm-dd hh24:mi:ss') \n");
		}*/
		//if (Utility.testString(map.get("modelId"))) {
		//	sql.append(" and  MATERIAL_GROUP_ID="+map.get("modelId")+"  \n");
		//}
		if (Utility.testString(map.get("activityCode"))) {
			sql.append(" and  ACTIVITY_CODE LIKE '%"+map.get("activityCode")+"%' \n");
		}
		if (Utility.testString(map.get("activityName"))) {
			sql.append(" and  ACTIVITY_NAME LIKE '%"+map.get("activityName")+"%' \n");
		}
		
		//map.put("proDate",proDateStr);
		//map.put("purDate", purDateStr);
		//map.put("dealerId", dealerId);
		//map.put("modelId", modelId);
		//map.put("activityCode", activityCode);
		//map.put("activityName", activityName);
		sql.append(" and sysdate between a.startdate and a.enddate\n");
		sql.append("    ORDER BY trim(A.ACTIVITY_CODE) desc  \n");

		PageResult<TtAsActivityPO> resList = pageQuery(TtAsActivityPO.class, sql
				.toString(), null,pageSize,curPage);
		return resList;
	}
	/**
	 * 
	 * @Title: queryActivityRepair
	 * @Description: TODO(查询服务活动维修项目)
	 * @param
	 * @return 设定文件
	 * @return List<TtAsActivityPO> 返回类型 
	 * @throws
	 */
	public List<TtAsActivityRepairitemExtPO> queryActivityRepair(String code,
			String dealerId,String modelId,String companyId) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT a.activity_code,t.*,("+getLabourParameter(dealerId,modelId,companyId)+" ) AS parameter_value,\n");
		sql.append("  ("+getLabourParameter(dealerId,modelId,companyId)+" )*normal_labor as sum\n");
		sql.append("  FROM TT_AS_ACTIVITY_REPAIRITEM t left outer join tt_as_activity a on t.activity_id=a.activity_id\n");
		sql.append("  WHERE  a.ACTIVITY_CODE = '" + code + "'");

		List<TtAsActivityRepairitemExtPO> resList = this.select(TtAsActivityRepairitemExtPO.class, sql.toString(), null);
		return resList;
	}

	/**
	 * 
	 * @Title: queryActivityPart
	 * @Description: TODO(查询服务活动配件)
	 * @param
	 * @return 设定文件
	 * @return List<TtAsActivityPartsPO> 返回类型 
	 * @throws
	 */
	public List<TtAsActivityPartsPO> queryActivityPart(String code,String vin,Double mileage,
			String dealerId) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT part_no,part_name,part_quantity,part_price,supplier_code,supplier_name,t.part_price*t.part_quantity as part_amount \n");
		sql.append("FROM TT_AS_ACTIVITY_PARTS t left outer join tt_as_activity a on t.activity_id=a.activity_id \n");
		sql.append("WHERE  a.ACTIVITY_CODE = '" + code + "' \n");
		// sql.append("order by T.ACTIVITY_ID desc ;");
		TmVehiclePO tvp = new TmVehiclePO();
		int month = 999999;  //相隔月数
		Date now = new Date();
		
		if (Utility.testString(vin)) {
			tvp.setVin(vin);
			List<TmVehiclePO> lsv = select(tvp);
			if (lsv!=null) {
				if (lsv.size()>0) {
					tvp = lsv.get(0);
					Date purchasedDate = tvp.getPurchasedDate();
					if (purchasedDate!=null) {
						String formatStyle ="yyyy-MM-dd";  
						SimpleDateFormat df = new SimpleDateFormat(formatStyle);  
						String d1 = df.format(purchasedDate);
						String d2 = df.format(now);
						month  = Utility.compareDate(d1,d2,1); //取得今日和保养开始时间的插值
					}
				}else {
					
				}
			}else {
				
			}
		}else {
			
		}
		List<TtAsActivityPartsPO> resList = this.select(TtAsActivityPartsPO.class, sql.toString(), null);
		List<TtAsActivityPartsPO> myList = new ArrayList<TtAsActivityPartsPO>();
		List<TtAsWrRuleListPO> ls = getPartGuaListByVin(vin,code);
		for (TtAsActivityPartsPO po : resList) {
			for(TtAsWrRuleListPO por : ls) {
				if (po.getPartNo().trim().equals(por.getPartCode().trim()) ) {
					if (mileage<por.getClaimMelieage()&&month<por.getClaimMonth()) {
						po.setIsGua(1);
					}else {
						po.setIsGua(0);
					}
				}
			}
			po.setIsGua(0);
			myList.add(po);
		}
		return myList;
	}

	/**
	 * 
	 * @Title: queryActivityOther
	 * @Description: TODO(查询服务活动其他项目)
	 * @param
	 * @return 设定文件
	 * @return List<TtAsActivityNetitemPO> 返回类型 
	 * @throws
	 */
	public List<TtAsActivityNetitemPO> queryActivityOther(String code,
			String dealerId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select t.*\n");
		sql
				.append("  FROM TT_AS_ACTIVITY_NETITEM t left outer join tt_as_activity a on t.activity_id=a.activity_id\n");
		sql.append("  WHERE  a.ACTIVITY_CODE = '" + code + "'\n");
		// sql.append(" ORDER BY T.ACTIVITY_ID desc");

		List<TtAsActivityNetitemPO> resList = this.select(
				TtAsActivityNetitemPO.class, sql.toString(), null);
		return resList;
	}

	/**
	 * 
	 * @Title: getRoItemWorkHoursQuery
	 * @Description: TODO(取得工单带有的工时)
	 * @param
	 * @param MantainBean
	 * @param
	 * @param curPage
	 * @param
	 * @param pageSize
	 * @param
	 * @return
	 * @param
	 * @throws Exception
	 *             设定文件
	 * @return PageResult<Map<String,Object>> 返回类型
	 * @throws
	 */
	public PageResult<Map<String, Object>> getRoItemWorkHoursQuery(
			TtAsActivityBean MantainBean,Map<String,String> map,AclUserBean user, int curPage, int pageSize)
			throws Exception {
		StringBuffer sql = new StringBuffer();
		sql
				.append("	select ww.ID as labourId,  ww.WR_LABOURCODE AS LABOUR_CODE, ww.WR_LABOURNAME AS CN_DES, ww.STD_LABOUR_HOUR AS LABOUR_HOUR,  \n");
		sql.append("( select count(*)\n" );
		sql.append("from TT_AS_WR_FOREAPPROVAL t1,tm_dealer td,TT_AS_WR_FOREAPPROVALITEM t2\n" );
		sql.append("where t1.dealer_id = td.dealer_id\n" );
		sql.append("and t2.status is not null\n" );
		sql.append("and t1.OEM_COMPANY_ID ="+GetOemcompanyId.getOemCompanyId(user)+"\n" );
		sql.append("and t1.id = t2.fid\n" );
		sql.append("and t1.ro_no='"+map.get("roNo")+"'\n" );
		sql.append("and t1.vin='"+map.get("vin")+"'\n" );
		sql.append("and t2.status="+Constant.PRECLAIM_AUDIT_01+"\n" ); //已同意
		sql.append("and t2.item_type="+Constant.PRE_AUTH_ITEM_01+"\n" ); //工时
		sql.append("and t2.item_code=ww.WR_LABOURCODE\n" );
		sql.append(") as fore, \n");
		sql.append("(case when instr(ww.WR_LABOURCODE,'"+Constant.SPEC_LABOUR_CODE+"')=1 then 1 else 0 end ) as is_spec \n");
		sql.append("    from TT_AS_RO_LABOUR ww \n");
		sql.append(" where 1=1 ");
		sql.append(" AND (IS_SEL='0' OR IS_SEL IS NULL) ");

		if (Utility.testString(MantainBean.getActivityId())) {// 活动ID
			sql.append("    AND  RO_ID=" + MantainBean.getActivityId() + " \n");
		}
		if (Utility.testString(MantainBean.getLabourCode())) {// 工时代码
			sql.append("    and  UPPER(ww.WR_LABOURCODE) like  UPPER('%"
					+ MantainBean.getLabourCode() + "%') \n");
		}
		if (Utility.testString(MantainBean.getCnDes())) {// 工时名称
			sql.append("    and  ww.WR_LABOURNAME like '%"
					+ MantainBean.getCnDes() + "%' \n");
		}
		sql.append("order by ww.WR_LABOURCODE desc  \n");
		PageResult<Map<String, Object>> ps = (PageResult<Map<String, Object>>) pageQuery(
				sql.toString(), null, getFunName(), pageSize, curPage);
		return ps;
	}

	/**
	 * 
	 * @Title: getRoPartsQuery
	 * @Description: TODO(取得工单带有的配件)
	 * @param
	 * @param MantainBean
	 * @param
	 * @param curPage
	 * @param
	 * @param pageSize
	 * @param
	 * @return
	 * @param
	 * @throws Exception
	 *             设定文件
	 * @return PageResult<Map<String,Object>> 返回类型
	 * @throws
	 */
	public PageResult<Map<String, Object>> getRoPartsQuery(
			TtAsActivityBean MantainBean, Map<String,String> map,AclUserBean user,int curPage, int pageSize)
			throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("	select A.ID AS PART_ID, A.PART_NO AS PART_CODE, PART_NAME ,a.PART_COST_PRICE AS CLAIM_PRICE,PART_QUANTITY  \n");
//		sql.append("( select count(*)\n" );
//		sql.append("from TT_AS_WR_FOREAPPROVAL t1,tm_dealer td,TT_AS_WR_FOREAPPROVALITEM t2\n" );
//		sql.append("where t1.dealer_id = td.dealer_id\n" );
//		sql.append("and t2.status is not null\n" );
//		sql.append("and t1.OEM_COMPANY_ID ="+GetOemcompanyId.getOemCompanyId(user)+"\n" );
//		sql.append("and t1.id = t2.fid\n" );
//		sql.append("and t1.ro_no='"+map.get("roNo")+"'\n" );
//		sql.append("and t1.vin='"+map.get("vin")+"'\n" );
//		sql.append("and t2.status="+Constant.PRECLAIM_AUDIT_01+"\n" ); //已同意
//		sql.append("and t2.item_type="+Constant.PRE_AUTH_ITEM_02+"\n" ); //配件
//		sql.append("and t2.item_code=a.PART_NO\n" );
//		sql.append(") as fore \n");
		sql.append("    from TT_AS_RO_REPAIR_PART a \n");
		sql.append(" where 1=1 ");
		sql.append(" AND (IS_SEL='0' OR IS_SEL IS NULL) ");
		if (Utility.testString(MantainBean.getActivityId())) {// 活动ID
			sql.append("    and  RO_ID=" + MantainBean.getActivityId() + " \n");
		}
		if (Utility.testString(MantainBean.getPartNo())) {// 配件代码
			sql.append("    and  UPPER(a.PART_NO) like  UPPER('%"
					+ MantainBean.getPartNo() + "%') \n");
		}
		if (Utility.testString(MantainBean.getPartName())) {// 配件名称
			sql.append("    and  a.PART_NAME like '%"
					+ MantainBean.getPartName() + "%' \n");
		}
		sql.append(" order by a.PART_NO desc  \n");
		PageResult<Map<String, Object>> ps = (PageResult<Map<String, Object>>) pageQuery(
				sql.toString(), null, getFunName(), pageSize, curPage);
		return ps;
	}

	/**
	 * 
	 * @Title: getRoOthersQuery
	 * @Description: TODO(取得工单带有的其他项目)
	 * @param
	 * @param MantainBean
	 * @param
	 * @param curPage
	 * @param
	 * @param pageSize
	 * @param
	 * @return
	 * @param
	 * @throws Exception
	 *             设定文件
	 * @return PageResult<Map<String,Object>> 返回类型
	 * @throws
	 */
	public PageResult<Map<String, Object>> getRoOthersQuery(
			TtAsActivityBean MantainBean, int curPage, int pageSize)
			throws Exception {
		StringBuffer sql = new StringBuffer();
		sql
				.append("	select ID AS FEE_ID, ADD_ITEM_CODE AS FEE_CODE, ADD_ITEM_NAME AS FEE_NAME,ADD_ITEM_AMOUNT AS FEE_AMOUNT,REMARK AS FEE_REMARK  \n");
		sql.append("    from TT_AS_RO_ADD_ITEM  \n");
		sql.append(" where 1=1 ");
		sql.append(" AND IS_SEL<>1 ");
		if (Utility.testString(MantainBean.getActivityId())) {// 活动ID
			sql.append("   AND RO_ID=" + MantainBean.getActivityId() + " \n");
		}
		sql.append("order by ADD_ITEM_CODE desc  \n");
		PageResult<Map<String, Object>> ps = (PageResult<Map<String, Object>>) pageQuery(
				sql.toString(), null, getFunName(), pageSize, curPage);
		return ps;
	}

	/**
	 * 
	 * @Title: getWorkingHoursInfoList
	 * @Description: TODO(工单带有的工时查询)
	 * @param
	 * @param roId
	 * @param
	 * @return 设定文件
	 * @return List<TtAsActivityBean> 返回类型 
	 * @throws
	 */
	public List<TtAsActivityBean> getWorkingHoursInfoList(String roId,Map<String,String> map,AclUserBean user) {
		List<Object> params = new LinkedList();
		StringBuilder sql = new StringBuilder();
		sql.append("  SELECT (CASE WHEN CHARGE_PARTITION_CODE='S' THEN "+Constant.PAY_TYPE_02+" ELSE "+Constant.PAY_TYPE_01+" END) AS PAY_TYPE,UPDATE_DATE,  CREATE_DATE, CREATE_BY, UPDATE_BY,WR_LABOURCODE AS ITEM_CODE, ID AS ITEM_ID,RO_ID AS  ACTIVITY_ID,WR_LABOURNAME AS ITEM_NAME, LABOUR_AMOUNT AS LABOR_FEE,nvl(to_char(STD_LABOUR_HOUR,'FM9999990.0099'),'0.00') AS NORMAL_LABOR,("+getLabourParameter(user.getDealerId(),map.get("modelId"),map.get("companyId"))+") AS PARAMETER_VALUE,  \n");
		//sql.append("  SELECT PAY_TYPE,UPDATE_DATE,  CREATE_DATE, CREATE_BY, UPDATE_BY,WR_LABOURCODE AS ITEM_CODE, ID AS ITEM_ID,RO_ID AS  ACTIVITY_ID,WR_LABOURNAME AS ITEM_NAME, LABOUR_AMOUNT AS LABOR_FEE,nvl(STD_LABOUR_HOUR,'0.00') AS NORMAL_LABOR,(LABOUR_PRICE) AS PARAMETER_VALUE,  \n");
//		sql.append("( select count(*)\n" );
//		sql.append("from TT_AS_WR_FOREAPPROVAL t1,tm_dealer td,TT_AS_WR_FOREAPPROVALITEM t2\n" );
//		sql.append("where t1.dealer_id = td.dealer_id\n" );
//		sql.append("and t2.status is not null\n" );
//		sql.append("and t1.OEM_COMPANY_ID ="+GetOemcompanyId.getOemCompanyId(user)+"\n" );
//		sql.append("and t1.id = t2.fid\n" );
//		sql.append("and t1.ro_no='"+map.get("roNo")+"'\n" );
//		sql.append("and t1.vin='"+map.get("vin")+"'\n" );
//		sql.append("and t2.status="+Constant.PRECLAIM_AUDIT_01+"\n" ); //已同意
//		sql.append("and t2.item_type="+Constant.PRE_AUTH_ITEM_01+"\n" ); //工时
//		sql.append("and t2.item_code=TT_AS_RO_LABOUR.WR_LABOURCODE\n" );
//		sql.append(") as fore, \n");
		sql.append("(case when instr(TT_AS_RO_LABOUR.WR_LABOURCODE,'"+Constant.SPEC_LABOUR_CODE+"')=1 then 1 else 0 end ) as is_spec \n");
		sql.append("  FROM TT_AS_RO_LABOUR \n");
		sql.append(" where 1=1");
		sql.append(" AND PAY_TYPE="+Constant.PAY_TYPE_02+" \n"); //OEM
		sql.append(" AND (IS_CLAIM=0 OR IS_CLAIM IS NULL) \n");
		if (Utility.testString(map.get("isSel"))){
			//sql.append(" and is_sel='1' ");
		}else {
			sql.append(" and (is_sel='0' or is_sel is null) ");
		}
		
		if (Utility.testString(roId)) {
			sql.append("  AND  RO_ID = ? \n");
			params.add(roId);
		}
		if (Utility.testString(map.get("itemIds"))) {
			sql.append(" AND ID IN ("+map.get("itemIds")+") \n");
		}
		sql.append(" order by ID desc ");
		List list = dao.select(TtAsActivityBean.class, sql.toString(), params);
		return list;
	}

	/**
	 * 
	 * @Title: getPartsList
	 * @Description: TODO(工单带有的配件查询)
	 * @param
	 * @param activityId
	 * @param
	 * @return 设定文件
	 * @return List<TtAsActivityBean> 返回类型 
	 * @throws
	 */
	public List<TtAsActivityBean> getPartsList(String roId,Map<String,String> map,AclUserBean user) {
		List<Object> params = new LinkedList();
		StringBuilder sql = new StringBuilder();
		//sql.append("  SELECT (CASE WHEN CHARGE_PARTITION_CODE='S' THEN "+Constant.PAY_TYPE_02+" ELSE "+Constant.PAY_TYPE_01+" END) AS PAY_TYPE,IS_GUA,UPDATE_DATE,  CREATE_DATE, CREATE_BY, UPDATE_BY, PART_NO, PART_NAME,PART_COST_AMOUNT AS PART_AMOUNT, RO_ID AS ACTIVITY_ID, PART_COST_PRICE AS PART_PRICE, PART_QUANTITY, ID AS PARTS_ID ,  \n");
		sql.append("  SELECT (CASE WHEN CHARGE_PARTITION_CODE='S' THEN "+Constant.PAY_TYPE_02+" ELSE "+Constant.PAY_TYPE_01+" END) AS PAY_TYPE,IS_GUA,UPDATE_DATE,  CREATE_DATE, CREATE_BY, UPDATE_BY, PART_NO, PART_NAME, RO_ID AS ACTIVITY_ID,  PART_QUANTITY, ID AS PARTS_ID ,  \n");
		sql.append("        PART_COST_PRICE AS PART_price , \n");
		sql.append(" PART_COST_AMOUNT as PART_AMOUNT\n");
//		sql.append("( select count(*)\n" );
//		sql.append("from TT_AS_WR_FOREAPPROVAL t1,tm_dealer td,TT_AS_WR_FOREAPPROVALITEM t2\n" );
//		sql.append("where t1.dealer_id = td.dealer_id\n" );
//		sql.append("and t2.status is not null\n" );
//		sql.append("and t1.OEM_COMPANY_ID ="+GetOemcompanyId.getOemCompanyId(user)+"\n" );
//		sql.append("and t1.id = t2.fid\n" );
//		sql.append("and t1.ro_no='"+map.get("roNo")+"'\n" );
//		sql.append("and t1.vin='"+map.get("vin")+"'\n" );
//		sql.append("and t2.status="+Constant.PRECLAIM_AUDIT_01+"\n" ); //已同意
//		sql.append("and t2.item_type="+Constant.PRE_AUTH_ITEM_02+"\n" ); //配件
//		sql.append("and t2.item_code=TT_AS_RO_REPAIR_PART.PART_NO\n" );
//		sql.append(") as fore \n");
		sql.append("  FROM TT_AS_RO_REPAIR_PART \n");
		sql.append("  WHERE  1=1  \n");
		sql.append(" AND PAY_TYPE="+Constant.PAY_TYPE_02+" \n"); //OEM
		sql.append(" AND (IS_CLAIM=0 OR IS_CLAIM IS NULL) \n");
		if (Utility.testString(map.get("isSel"))){
			//sql.append(" and is_sel='1' ");
		}else {
			sql.append(" and (is_sel='0' or is_sel is null) ");
		}
		if (Utility.testString(roId)) {
			sql.append("  AND  RO_ID = ? \n");
			params.add(roId);
		}
		if (Utility.testString(map.get("partIds"))) {
			sql.append(" AND ID IN ("+map.get("partIds")+") \n");
		}
		sql.append(" order by ID desc ");
		List list = dao.select(TtAsActivityBean.class, sql.toString(), params);
		return list;
	}

	/**
	 * 
	 * @Title: getNetItemList
	 * @Description: TODO(工单带有的其他项目查询)
	 * @param
	 * @param activityId
	 * @param
	 * @return 设定文件
	 * @return List<TtAsActivityBean> 返回类型 
	 * @throws
	 */
	public List<TtAsActivityBean> getNetItemList(String roId,Map<String,String> map,AclUserBean user) {
		List<Object> params = new LinkedList();
		StringBuilder sql = new StringBuilder();
		sql.append("  select (CASE WHEN CHARGE_PARTITION_CODE='S' THEN "+Constant.PAY_TYPE_02+" ELSE "+Constant.PAY_TYPE_01+" END) AS PAY_TYPE,ID,  UPDATE_DATE, REMARK,  CREATE_DATE, CREATE_BY, UPDATE_BY, ADD_ITEM_CODE AS ITEM_CODES, ADD_ITEM_NAME AS ITEM_DESC, ADD_ITEM_AMOUNT AS AMOUNT,REMARK, RO_ID AS ACTIVITY_ID  \n");
//		sql.append("( select count(*)\n" );
//		sql.append("from TT_AS_WR_FOREAPPROVAL t1,tm_dealer td,TT_AS_WR_FOREAPPROVALITEM t2\n" );
//		sql.append("where t1.dealer_id = td.dealer_id\n" );
//		sql.append("and t2.status is not null\n" );
//		sql.append("and t1.OEM_COMPANY_ID ="+GetOemcompanyId.getOemCompanyId(user)+"\n" );
//		sql.append("and t1.id = t2.fid\n" );
//		sql.append("and t1.ro_no='"+map.get("roNo")+"'\n" );
//		sql.append("and t1.vin='"+map.get("vin")+"'\n" );
//		sql.append("and t2.status="+Constant.PRECLAIM_AUDIT_01+"\n" ); //已同意
//		sql.append("and t2.item_type="+Constant.PRE_AUTH_ITEM_03+"\n" ); //其他项目
//		sql.append("and t2.item_code=TT_AS_RO_ADD_ITEM.ADD_ITEM_CODE \n" );
//		sql.append(") as fore \n");
		sql.append("  FROM TT_AS_RO_ADD_ITEM  \n");
		sql.append(" WHERE 1=1 \n");
		sql.append(" AND PAY_TYPE="+Constant.PAY_TYPE_02+" \n"); //OEM
		sql.append(" AND (IS_CLAIM=0 OR IS_CLAIM IS NULL) \n");
		if (Utility.testString(map.get("isSel"))){
			//sql.append(" and is_sel=1 ");
		}else {
			sql.append(" and (is_sel=0 or is_sel is null) ");
		}
		if (Utility.testString(map.get("otherIds"))) {
			sql.append(" AND ID IN ("+map.get("otherIds")+") \n");
		}
		if (Utility.testString(roId)) {
			sql.append("  AND  RO_ID = ? \n");
			params.add(roId);
			sql.append(" \n");
			sql.append("  ORDER BY ID desc \n");
		}
		
		List list = dao.select(TtAsActivityBean.class, sql.toString(), params);
		return list;
	}

	/**
	 * 
	 * @Title: roItemWorkHoursOption
	 * @Description: TODO(工单新增工时)
	 * @param
	 * @param groupIdsArray
	 * @param
	 * @param labourCodeArray
	 * @param
	 * @param cnDesArray
	 * @param
	 * @param labourHourArray
	 * @param
	 * @param RepairitemPO
	 *            设定文件
	 * @return void 返回类型
	 * @throws
	 */
	public void roItemWorkHoursOption(String roId,String[] groupIdsArray,AclUserBean logonUser) {
		Date date = new Date();
		try{
		for (int i = 0; i < groupIdsArray.length; i++) {
			TtAsRoLabourPO tarp = new TtAsRoLabourPO();
			tarp.setId(Utility.getLong(groupIdsArray[i]));
			TtAsRoLabourPO tarpUp = new TtAsRoLabourPO();
			tarpUp.setId(Utility.getLong(groupIdsArray[i]));
			//tarpUp.setRoId(Utility.getLong(roId));
			tarpUp.setUpdateBy(logonUser.getUserId());
			tarpUp.setUpdateDate(date);
			tarpUp.setIsSel("1"); //1为被选择
			//RepairitemPO.setLabourId(Long.parseLong(SequenceManager
					//.getSequence("")));
			//RepairitemPO.setWrLabourcode((labourCodeArray[i]));
			//RepairitemPO.setWrLabourname((cnDesArray[i]));
			//RepairitemPO.setNormalLabor(Float.parseFloat(labourHourArray[i]));
			//insert(RepairitemPO);
			update(tarp,tarpUp);
		}
		}catch(Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * 
	 * @Title: deleteItemWorkHoursOption
	 * @Description: TODO(工单删除工时)
	 * @param
	 * @param RepairitemPO
	 *            设定文件
	 * @return void 返回类型
	 * @throws
	 */
	public void deleteItemWorkHoursOption(TtAsRoLabourPO RepairitemPO) {
		TtAsRoLabourPO tup = new TtAsRoLabourPO();
		tup.setId(RepairitemPO.getId());
		tup.setIsSel("");
		update(RepairitemPO,tup);
	}

	/**
	 * 
	 * @Title: roItemPartsOption
	 * @Description: TODO(工单增加配件)
	 * @param
	 * @param partsIdArray
	 * @param
	 * @param partNoArray
	 * @param
	 * @param partNameArray
	 * @param
	 * @param partsQuantityArray
	 * @param
	 * @param claimPriceArray
	 * @param
	 * @param supplierCodeArray
	 * @param
	 * @param supplierNameArray
	 * @param
	 * @param PartsPO
	 *            设定文件
	 * @return void 返回类型
	 * @throws
	 */
	public void roItemPartsOption(String roId,String[] partsIdArray,AclUserBean logonUser) {
		Date date = new Date();
		try {
		for (int i = 0; i < partsIdArray.length; i++) {
			TtAsRoRepairPartPO tarrpp = new TtAsRoRepairPartPO();
			tarrpp.setId(Utility.getLong(partsIdArray[i]));
			TtAsRoRepairPartPO tarrppUp = new TtAsRoRepairPartPO();
			//tarrppUp.setId(Utility.getLong(partsIdArray[i]));
			tarrppUp.setUpdateBy(logonUser.getUserId());
			tarrppUp.setUpdateDate(date);
			tarrppUp.setIsSel("1");
			
			update(tarrpp,tarrppUp);
		}
		}catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 
	 * @Title: deleteItemPartsOption
	 * @Description: TODO(工单删除配件)
	 * @param
	 * @param PartsPO
	 *            设定文件
	 * @return void 返回类型
	 * @throws
	 */
	public void deleteItemPartsOption(TtAsRoRepairPartPO PartsPO) {
		TtAsRoRepairPartPO tup = new TtAsRoRepairPartPO();
		tup.setId(PartsPO.getId());
		tup.setIsSel("");
		update(PartsPO,tup);
	}

	/**
	 * 
	 * @Title: roItemOthersOption
	 * @Description: TODO(工单增加其他项目)
	 * @param
	 * @param idArray
	 * @param
	 * @param itemCodeArray
	 * @param
	 * @param itemDescArray
	 * @param
	 * @param NetitemPO
	 *            设定文件
	 * @return void 返回类型
	 * @throws
	 */
	public void roItemOthersOption(String roId,String[] idArray,AclUserBean logonUser) {
		Date date = new Date();
		try {
		for (int i = 0; i < idArray.length; i++) {
			TtAsRoAddItemPO taraip = new TtAsRoAddItemPO();
			taraip.setId(Utility.getLong(idArray[i]));
			TtAsRoAddItemPO taraipUp = new TtAsRoAddItemPO();
			taraipUp.setId(Utility.getLong(idArray[i]));
			taraipUp.setRoId(Utility.getLong(roId));
			taraipUp.setUpdateBy(logonUser.getUserId());
			taraipUp.setUpdateDate(date);
			taraipUp.setIsSel(1);
			
			update(taraip,taraipUp);
		}
		}catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 
	 * @Title: deleteItemOthersOption
	 * @Description: TODO(工单删除其他项目)
	 * @param
	 * @param NetitemPO
	 *            设定文件
	 * @return void 返回类型
	 * @throws
	 */
	public void deleteItemOthersOption(TtAsRoAddItemPO NetitemPO) {
		TtAsRoAddItemPO tup = new TtAsRoAddItemPO();
		tup.setId(NetitemPO.getId());
		tup.setIsSel(0);
		update(NetitemPO,tup);
	}

	/**
	 * 
	 * @Title: queryRepairOrder
	 * @Description: TODO(查询工单)
	 * @param
	 * @param map
	 * @param
	 * @return 设定文件
	 * @return List<TtAsRepairOrderExtPO> 返回类型 
	 * @throws
	 */
	public PageResult<TtAsRepairOrderExtPO> queryRepairOrder(
			Map<String, String> map, int pageSize, int curPage) {
		//如果是服务活动的话，刚执行费用查询区分判断
		String con = "select * from tt_as_activity a where a.activity_code = (select o.cam_code from tt_as_repair_order o where o.id ="+map.get("roId")+")\n";
		List<TtAsActivityPO> list = this.select(TtAsActivityPO.class, con, null);
		boolean b = true ;
		if(list.size()>0){
			//如果是服务活动，并且是免费保养，则费用从这里带出。
			if((((TtAsActivityPO)list.get(0)).getActivityKind()).toString().equals(Constant.SERVICEACTIVITY_KIND_02.toString())){
				b = false ;
			}
		}
		
		StringBuffer sqlStr= new StringBuffer();
		sqlStr.append("select (select count(1) from tt_as_wr_application where ro_no=r.ro_no) as in_claim,v.claim_tactics_id as claim_tactics_id,v1.model_id as model_id,r.*,ac.activity_name as campaign_name,nvl(ac.is_fixfee,0) as cam_fix,nvl(v.license_no,'') as license_no,v1.BRAND_CODE as brand_code,v1.SERIES_CODE as series_code,v1.MODEL_CODE as model_code,\n" );
		sqlStr.append("v1.BRAND_NAME as brand_name,v1.SERIES_NAME as series_name,nvl(v1.PACKAGE_CODE,'') as model_name,v.engine_no as engine_no_v,\n" );
		sqlStr.append("nvl(v.rearaxle_no,'') as rearaxle_no,v.yieldly as yieldly,nvl(v.gearbox_no,'') as gearbox_no,nvl(v.transfer_no,'') as transfer_no,v.purchased_date as purchased_date,v.product_date, \n" );
		sqlStr.append("o.start_time as start_time,o.end_time as end_time,o.out_person as out_person,\n");
		sqlStr.append("o.out_site as out_site,o.out_licenseno as out_licenseno,o.from_adress as from_adress,\n");
		sqlStr.append("o.end_adress as end_adress,o.out_mileage as out_mileages,\n");
		if(b){
			sqlStr.append("ac.activity_fee as campaign_fee\n");
		}else{
			sqlStr.append("(SELECT wg.FREE\n");
			sqlStr.append("          FROM TM_VHCL_MATERIAL_GROUP mg,\n");  
			sqlStr.append("               TT_AS_WR_MODEL_ITEM    wm,\n");  
			sqlStr.append("               TT_AS_WR_MODEL_GROUP   wg,\n");  
			sqlStr.append("               TM_VHCL_MATERIAL_GROUP mg2\n");  
			sqlStr.append("         WHERE mg.group_id = wm.model_id\n");  
			sqlStr.append("           and mg.group_id = v.model_id\n");  
			sqlStr.append("           AND wm.wrgroup_id = wg.wrgroup_id\n");  
			sqlStr.append("           and mg.parent_group_id = mg2.group_id\n");  
			sqlStr.append("           and wg.wrgroup_type = '10451001') campaign_fee\n");
		}
		sqlStr.append(" from tt_as_repair_order r left outer join tm_vehicle v on r.vin=v.vin\n" );
		sqlStr.append("left outer join vw_material_group v1 on v.package_id=v1.package_ID \n");
		sqlStr.append("left outer join tt_as_wr_outrepair o on r.ro_no=o.ro_no \n");
		sqlStr.append("left outer join tm_dealer d on r.dealer_id=d.dealer_id \n"); //YH 2010.11.30
		//sqlStr.append("left outer join tt_dealer_actual_sales da on da.vehicle_id");
		sqlStr.append("LEFT OUTER JOIN TT_AS_ACTIVITY AC ON AC.ACTIVITY_CODE=R.CAM_CODE AND AC.IS_DEL=0 \n");
		/*map.put("balanceDateStart", balanceDateStart);
		map.put("balanceDateEnd", balanceDateEnd);
		map.put("repairType", repairType);
		map.put("modelId", modelId);
		map.put("licenseNo", licenseNo);*/
		// sqlStr.append(" left outer join TM_DEALER b on
		// a.dealer_id=b.dealer_id \n");
		sqlStr.append(" where 1=1 ");
		
		//判断登陆系统(工单是否配件大类判断)
		List<Map<String,Object>> ListCode =   this.queryTcCode();
	    Integer code = Integer.valueOf(ListCode.get(0).get("CODE_ID").toString());
	    if(code==Constant.chana_wc){
			//sqlStr.append(" AND (r.part_flag<>0 or r.part_flag is null)\n");
	    	sqlStr.append(" AND r.part_flag=1\n");
	    }	
		if(Utility.testString(map.get("roId"))) {
			sqlStr.append(" and r.ID="+map.get("roId")+" \n");
		}
		if(Utility.testString(map.get("dealerId"))) {
			sqlStr.append(" and d.DEALER_ID="+map.get("dealerId")+" \n");
		}
		if(Utility.testString(map.get("roNo"))) {
			sqlStr.append(" and r.RO_NO LIKE '%"+map.get("roNo")+"%' \n");
		}
		if(Utility.testString(map.get("repairType"))) {
			sqlStr.append(" and r.REPAIR_TYPE_CODE = '"+map.get("repairType")+"' \n");
		}
		if(Utility.testString(map.get("roCreateDate"))) {
			sqlStr.append(" and r.RO_CREATE_DATE >= to_date('"+map.get("roCreateDate")+"', 'yyyy-mm-dd hh24:mi:ss')\n");
		}
		if(Utility.testString(map.get("deliveryDate"))) {
			sqlStr.append(" AND r.DELIVERY_DATE <= to_date('"+map.get("deliveryDate")+" 23:59:59', 'yyyy-mm-dd hh24:mi:ss') \n");
		}
		//结算开始时间
		if(Utility.testString(map.get("balanceDateStart"))) {
			sqlStr.append(" and r.UPDATE_DATE >= to_date('"+map.get("balanceDateStart")+"', 'yyyy-mm-dd hh24:mi:ss')\n");
		}
		//结算结束时间
		if(Utility.testString(map.get("balanceDateEnd"))) {
			sqlStr.append(" AND r.UPDATE_DATE <= to_date('"+map.get("balanceDateEnd")+" 23:59:59', 'yyyy-mm-dd hh24:mi:ss') \n");
		}
		
		
		//车牌号
		if(Utility.testString(map.get("licenseNo"))) {
			sqlStr.append(" and v.LICENSE_NO LIKE '%"+map.get("licenseNo")+"%' \n");
		}
		//车型--去掉
		if(Utility.testString(map.get("modelId"))) {
			sqlStr.append(" and V.MODEL_ID="+map.get("modelId")+" \n");
		}
		if (Utility.testString(map.get("customerName"))) {
			sqlStr.append(" and r.owner_name like '%"+map.get("customerName")+"%' \n");
		}
		if(Utility.testString(map.get("isForl"))) {
			sqlStr.append(" and r.FORL_STATUS = '"+map.get("isForl")+"' \n");
		}
		if(Utility.testString(map.get("roStatus"))) {
			sqlStr.append(" and r.RO_STATUS = '"+map.get("roStatus")+"' \n");
		}
		if(Utility.testString(map.get("isApp"))) {
			sqlStr.append(" and NOT EXISTS (SELECT RO_NO FROM TT_AS_WR_APPLICATION A WHERE A.RO_NO=R.RO_NO) \n");
		}
		/*if(Utility.testString(map.get("vin"))) {
			sqlStr.append(" and r.VIN LIKE '%"+map.get("vin")+"%' \n");
		}*/
		// 车辆VIN码
		if (Utility.testString(map.get("vin"))) {// VIN不为空
			sqlStr.append(GetVinUtil.getVins(map.get("vin").replaceAll("'",
					"\''"), "r"));
		}
		if (Utility.testString(map.get("createDateStr"))) {
			sqlStr.append(" and r.CREATE_DATE >= to_date('"+map.get("createDateStr")+"', 'yyyy-mm-dd hh24:mi:ss')\n");
		}
		if (Utility.testString(map.get("createDateEnd"))) {
			sqlStr.append(" AND r.CREATE_DATE <= to_date('"+map.get("createDateEnd")+" 23:59:59', 'yyyy-mm-dd hh24:mi:ss') \n");
		}
		if(Utility.testString(map.get("isApp"))) {
			sqlStr.append(" and NOT EXISTS (SELECT RO_NO FROM tt_as_repair_order ro WHERE ro.RO_NO=R.RO_NO and RO.FREE_TIMES>1 AND RO.REPAIR_TYPE_CODE="+Constant.REPAIR_TYPE_04+" ) \n");
		}
		sqlStr.append("    and NOT EXISTS (SELECT RO_NO  FROM tt_as_repair_order ro  WHERE ro.RO_NO = R.RO_NO  and RO.Accredit_Amount=1 and ro.approval_yn=1  AND RO.REPAIR_TYPE_CODE = "+Constant.REPAIR_TYPE_04+")");
		sqlStr.append("  order by r.update_date desc ");
		return pageQuery(TtAsRepairOrderExtPO.class, sqlStr.toString(), null,
				pageSize, curPage);
	}
	
	/**
	 * Iverson update By 2010-12-16
	 * @param map
	 * @param pageSize
	 * @param curPage
	 * @return
	 */
	public PageResult<TtAsWrForeapprovalBean> queryRepairOrder1(Long userId,
			Map<String, String> map, int pageSize, int curPage) {
		StringBuffer sqlStr= new StringBuffer();
		List<Object> params = new LinkedList<Object>();
		params.add(map.get("orgCode"));
		sqlStr.append("select w.*,nvl(q.start_mileage,0) as start_mileage,nvl(q.end_mileage,0) as end_mileage,nvl(q.min_days,0) as min_days,nvl(q.max_days,0) as max_days,(select dealer_name from tm_dealer d where d.dealer_id=w.dealer_id) as dealer_name,(to_date(to_char(w.start_time,'yyyy-MM-dd'),'yyyy-MM-dd')-tv.purchased_date) AS differencestime from Tt_As_Wr_Foreapproval w,tm_dealer t,tt_as_wr_qamaintain q,tm_vehicle tv where 1=1 and tv.vin=w.vin and w.dealer_id=t.dealer_id and w.free_time = q.free_times(+)");
		if(Utility.testString(map.get("roNo"))) {
		sqlStr.append(" and w.ro_no like '%"+map.get("roNo")+"%' ");
		}
		if(Utility.testString(map.get("repairType"))) {
		sqlStr.append(" and w.APPROVAL_TYPE="+map.get("repairType")+" ");
		}
		if(Utility.testString(map.get("vin"))) {
		sqlStr.append(" and w.vin like '%"+map.get("vin")+"%' ");
		}
		if(Utility.testString(map.get("isForl"))) {
		sqlStr.append(" and w.REPORT_STATUS="+map.get("isForl")+" ");
		}
		if(Utility.testString(map.get("province"))) {
			sqlStr.append(" and t.PROVINCE_ID="+map.get("province")+" ");
		}
		if(Utility.testString(map.get("dealerId"))) {
			sqlStr.append(" and w.dealer_id in ("+map.get("dealerId")+") ");
		}
		sqlStr.append(" and w.REPORT_STATUS="+Constant.RO_FORE_01+""); 
		if(Utility.testString(map.get("orgCode"))) {
			sqlStr.append("   and w.dealer_id in (select ro.dealer_id from TM_DEALER_ORG_RELATION ro where ro.org_id in (select o.org_id from tm_org o where 1=1 ");
			if(map.get("orgCode")!=null&&!"".equals(map.get("orgCode"))){
			      String[] temp=map.get("orgCode").split(",");
			      String str="";
			      if(temp.length>0){
			        sqlStr.append(" and (");
			        for(int count=0;count<temp.length;count++){
			          str+=" o.org_code='"+temp[count].replaceAll("'", "\''")+"' or\n";
			        }
			        sqlStr.append(str.substring(0, str.length()-3));
			        sqlStr.append(")\n");
			      }
			    }
		sqlStr.append(")) ");
		}
		sqlStr.append(UserProvinceRelation.getDealerIds(userId, "t")); 
		sqlStr.append(" order by w.create_date desc  ");
		System.out.println("sqlsql=="+sqlStr.toString());
		return pageQuery(TtAsWrForeapprovalBean.class, sqlStr.toString(), null,
				pageSize, curPage);
	}
	public PageResult<TtAsWrForeapprovalBean> queryRepairOrderOEM(
			Map<String, String> map, int pageSize, int curPage) {
		StringBuffer sqlStr= new StringBuffer();
		sqlStr.append("select w.*,(select dealer_name from tm_dealer d where d.dealer_id=w.dealer_id) as dealer_name,u.name as audit_name from Tt_As_Wr_Foreapproval w,tc_user u where 1=1 ");
		sqlStr.append(" and w.audit_person = u.user_id\n");
		if(Utility.testString(map.get("roNo"))) {
		sqlStr.append(" and w.ro_no like '%"+map.get("roNo")+"%' ");
		}
		if(Utility.testString(map.get("repairType"))) {
		sqlStr.append(" and w.APPROVAL_TYPE="+map.get("repairType")+" ");
		}
		if(Utility.testString(map.get("vin"))) {
		sqlStr.append(" and w.vin like '%"+map.get("vin")+"%' ");
		}
		if(Utility.testString(map.get("isForl"))) {
		sqlStr.append(" and w.REPORT_STATUS="+map.get("isForl")+" ");
		}
		if(Utility.testString(map.get("auditName"))){
			sqlStr.append(" and u.name like '%").append(map.get("auditName")).append("%'\n");
		}
		if(Utility.testString(map.get("approveDate"))){
			sqlStr.append(" and w.update_date >= to_date('"
					+map.get("approveDate")
					+"','yyyy-MM-dd hh24:mi:ss')\n");
		}
		if(Utility.testString(map.get("approveDate2"))){
			sqlStr.append(" and w.update_date <= to_date('"
					+map.get("approveDate2")
					+" 23:59:59','yyyy-MM-dd hh24:mi:ss')\n");
		}
		if(Utility.testString(map.get("dealerId"))) {
			sqlStr.append(" and w.dealer_id="+map.get("dealerId")+" ");
			}
		//sqlStr.append(" and w.REPORT_STATUS="+Constant.RO_FORE_01+""); 
		if(Utility.testString(map.get("orgCode"))) {
			sqlStr.append(" and w.dealer_id in (select d.dealer_id from tm_dealer  d where d.company_id in (select o.company_id from tm_org o where o.org_code='"+map.get("orgCode")+"'))");
		}
		sqlStr.append(" order by w.report_status  ");
		return pageQuery(TtAsWrForeapprovalBean.class, sqlStr.toString(), null,
				pageSize, curPage);
	}
	public PageResult<TtAsWrForeapprovalBean> queryRepairOrderDealer(
			Map<String, String> map, int pageSize, int curPage) {
		StringBuffer sqlStr= new StringBuffer();
		sqlStr.append("select w.*,(select dealer_name from tm_dealer d where d.dealer_id=w.dealer_id) as dealer_name,(select name from tc_user where user_id = w.audit_person) as audit_name from Tt_As_Wr_Foreapproval w where 1=1 ");
		if(Utility.testString(map.get("roNo"))) {
		sqlStr.append(" and w.ro_no like '%"+map.get("roNo")+"%' ");
		}
		if(Utility.testString(map.get("repairType"))) {
		sqlStr.append(" and w.APPROVAL_TYPE="+map.get("repairType")+" ");
		}
		if(Utility.testString(map.get("vin"))) {
		sqlStr.append(" and w.vin='"+map.get("vin")+"' ");
		}
		if(Utility.testString(map.get("isForl"))) {
		sqlStr.append(" and w.REPORT_STATUS="+map.get("isForl")+" ");
		}
		if(Utility.testString(map.get("approveDate"))){
			sqlStr.append(" and w.update_date >= to_date('"
					+map.get("approveDate")
					+"','yyyy-MM-dd hh24:mi:ss')\n");
		}
		if(Utility.testString(map.get("approveDate2"))){
			sqlStr.append(" and w.update_date <= to_date('"
					+map.get("approveDate2")
					+" 23:59:59','yyyy-MM-dd hh24:mi:ss')\n");
		}
		if(Utility.testString(map.get("dealerId"))) {
			sqlStr.append(" and w.dealer_id="+map.get("dealerId")+" ");
			}
		//sqlStr.append(" and w.REPORT_STATUS="+Constant.RO_FORE_01+"");
		sqlStr.append(" order by w.create_date desc  ");
		return pageQuery(TtAsWrForeapprovalBean.class, sqlStr.toString(), null,
				pageSize, curPage);
	}
	
	
	
	
	/*
	 * 根据工单号查询 维修项目细则－附加项目
	 * @param roNo
	 * @return List<TtAsRoAddItemPO>
	 */
	public List<TtAsRoAddItemPO> queryAddItem(String roNo,String id){
		StringBuffer sql = new StringBuffer();
		sql.append("select * \n");
		//sql.append("select i.add_item_code,i.add_item_name,i.charge_partition_code \n");
		sql.append(" from tt_as_ro_add_item i \n");
		sql.append("where 1=1 \n");
		// 工单号必须唯一
		if(roNo!=null && !("").equals(roNo)){
			sql.append(" and i.ro_id=");
			sql.append(" (select id from tt_as_repair_order where ro_no='"+roNo+"' \n)");
		}
		if(Utility.testString(id)){
			sql.append(" and i.ro_id="+id+" \n");
		}
		sql.append(" order by i.add_item_code desc \n");
		return dao.select(TtAsRoAddItemPO.class,sql.toString(),null);
	}
	
	/*******************************Iverson add 2010-11-14 此功能是判断如果工单生成了索赔单就必须先删除索赔单再删除工单*****************/
	/**
	 * 根据工单ID查询工单号
	 */
	public List getRoNoById(TtAsRepairOrderPO po){
		List list = dao.select(po);
		return list;
	}
	/**
	 * Iverson add with 2010-11-14
	 * 查询工单下面有没生成有索赔单
	 * @param roNo
	 * @return 
	 */
	public long queryTtAsWrApplication(String roNo) throws Exception{
		POFactory factory = POFactoryBuilder.getInstance();
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) as count\n");
		sql.append(" from tt_as_wr_application i \n");
		sql.append("where i.ro_no='"+roNo+"' \n");
		List list=factory.select(sql.toString(), null,new DAOCallback(){
			public Long wrapper(ResultSet rs, int idx){
				Long count=0L;
				try{
					count=rs.getBigDecimal("count").longValue();
				}catch(Exception e){
					e.printStackTrace();
				}
				return count;	
			}
		}); 
		return (Long)list.get(0);
	}
	/************************Iverson add 2010-11-14 此功能是判断如果工单生成了索赔单就必须先删除索赔单再删除工单***********************************************/
	
	/*
	 * 根据工单号查询 维修项目细则－维修项目
	 * @param roNo
	 * @return List<TtAsRoAddItemPO>
	 */
	public List<TtAsRoLabourPO> queryRepairitem(String roNo,String id){
		StringBuffer sql = new StringBuffer();
		sql.append("select * \n");
		//sql.append("select l.labour_name,l.std_labour_hour,l.assign_labour_hour, \n");
		//sql.append(" l.worker_type_code,l.charge_partition_code \n");
		sql.append(" from tt_as_ro_labour l \n");
		sql.append("where 1=1 \n");
		// 工单号必须唯一
		if(roNo!=null && !("").equals(roNo)){
			sql.append(" and l.ro_id=");
			sql.append(" (select id from tt_as_repair_order where ro_no='"+roNo+"' \n)");
		}
		if(Utility.testString(id)){
			sql.append(" and l.ro_id="+id+" \n");
		}
		sql.append(" order by l.labour_name desc \n");
		return dao.select(TtAsRoLabourPO.class,sql.toString(),null);
	}
	
	/*
	 * 根据工单号查询 维修项目细则－维修零件
	 * @param roNo
	 * @return List<TtAsRoAddItemPO>
	 */
	public List<TtAsRoRepairPartPO> queryRepairPart(String roNo,String id){
		StringBuffer sql = new StringBuffer();
		sql.append("select * \n");
		//sql.append("select p.part_name,p.part_quantity,p.unit_code,p.charge_partition_code \n");
		sql.append(" from tt_as_ro_repair_part p \n");
		sql.append("where 1=1 \n");
		// 工单号必须唯一
		if(roNo!=null && !("").equals(roNo)){
			sql.append(" and p.ro_id=");
			sql.append(" (select id from tt_as_repair_order where ro_no='"+roNo+"' \n)");
		}
		if(Utility.testString(id)){
			sql.append(" and p.ro_id="+id+" \n");
		}
		sql.append(" order by p.part_name desc \n");
		return dao.select(TtAsRoRepairPartPO.class,sql.toString(),null);
	}
	/**
	 * 
	* @Title: queryRoById 
	* @Description: TODO(根据ID查询工单信息) 
	* @param @param id
	* @param @return    设定文件 
	* @return TtAsRepairOrderExtPO    返回类型 
	* @throws
	 */
	public TtAsRepairOrderExtBean queryRoById(String id) {
		StringBuffer sql = new StringBuffer();
		sql.append("select p.*,v.yieldly,v.product_date,ac.activity_name as campaign_name,nvl(ac.is_fixfee,0) as cam_fix,g.model_id as model_id,g.series_id as series_id,g.brand_id as brand_id, \n");
		sql.append("g.model_name as model_name,g.series_name as series_name,g.brand_name as brand_name, \n");
		sql.append("d.dealer_name as dealer_name, d.dealer_id as dealer_id,\n");
		sql.append("o.start_time as start_time,o.end_time as end_time,o.out_person as out_person,\n");
		sql.append("o.out_site as out_site,o.out_licenseno as out_licenseno,o.from_adress as from_adress,\n");
		sql.append("o.end_adress as end_adress,o.out_mileage as out_mileages \n");
		sql.append(" from tt_as_repair_order p \n");
		sql.append(" left outer join tt_as_wr_outrepair o on p.ro_no=o.ro_no \n");
		sql.append(" left outer join tm_vehicle v on p.vin=v.vin \n ");
		sql.append(" left outer join VW_MATERIAL_GROUP g on v.package_id=g.package_id \n");
		sql.append(" left outer join tm_dealer d on p.dealer_code=d.dealer_code \n");
		sql.append("LEFT OUTER JOIN TT_AS_ACTIVITY AC ON AC.ACTIVITY_CODE=p.CAM_CODE AND AC.IS_DEL=0 \n");
		// 工单ID
		if(Utility.testString(id)){
			sql.append(" where p.id="+id+"\n");
		}
		List<TtAsRepairOrderExtBean> ls = dao.select(TtAsRepairOrderExtBean.class,sql.toString(),null);
		TtAsRepairOrderExtBean extPo = new TtAsRepairOrderExtBean();
		if (ls!=null) {
			if(ls.size()>0) {
				extPo = ls.get(0);
			}
		}
		return extPo;
	}
	/**
	 * 
	* @Title: queryInfoById 
	* @Description: TODO(根据ID查询质量信息跟踪卡) 
	* @param @return    设定文件 
	* @return TtAsWrInformationqualityExtPO    返回类型 
	* @throws
	 */
	public TtAsWrInformationqualityExtPO queryInfoByClaimId (String id) {
		StringBuffer sql= new StringBuffer();
		sql.append("select * from tt_as_wr_informationquality\n" );
		sql.append("where 1=1 \n");
		//if(Utility.testString(id)) {
			sql.append(" and claim_id="+id+"");
		//}
		TtAsWrInformationqualityExtPO tawiep = new  TtAsWrInformationqualityExtPO();
		List<TtAsWrInformationqualityExtPO> ls = select(TtAsWrInformationqualityExtPO.class,sql.toString(),null);
		if (ls!=null) {
			if (ls.size()>0) {
				tawiep = ls.get(0);
			}
		}
		return tawiep;
	}
	
	/**
	 * 
	* @Title: queryInfoByClaimInfoId 
	* @Description: TODO(根据质量信息卡ID查询质量信息卡) 
	* @param @param id
	* @param @return    设定文件 
	* @return TtAsWrInformationqualityExtPO    返回类型 
	* @throws
	 */
	public TtAsWrInformationqualityExtPO queryInfoByClaimInfoId (String id) { 
		StringBuffer sql= new StringBuffer();
		sql.append("select * from tt_as_wr_informationquality\n" );
		sql.append("where 1=1 \n");
		//if(Utility.testString(id)) {
			sql.append(" and id="+id+"");
		//}
		TtAsWrInformationqualityExtPO tawiep = new  TtAsWrInformationqualityExtPO();
		List<TtAsWrInformationqualityExtPO> ls = select(TtAsWrInformationqualityExtPO.class,sql.toString(),null);
		if (ls!=null) {
			if (ls.size()>0) {
				tawiep = ls.get(0);
			}
		}
		return tawiep;
	}
	/**
	 * add by liuqiang
	* @Title: queryManage 
	* @Description: TODO(根据工单ID查询维修工单辅料) 
	* @param @param id 工单ID
	* @param @return    设定文件 
	* @return List<TtAsRoManagePO>    返回类型 
	* @throws
	 */
	public List<TtAsRoManagePO> queryManage(Long id) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * \n");
		sql.append(" from tt_as_ro_manage p \n");
		sql.append("where 1=1 \n");
		sql.append(" and p.ro_id="+id+" \n");
		return dao.select(TtAsRoManagePO.class, sql.toString(), null);
	}
	public List<LabourPartBean> queryLabourPartBean(
			String vin, int pageSize, int curPage) {
		StringBuilder sql= new StringBuilder();
		sql.append("select a.RO_CREATE_DATE,a.DEALER_CODE,a.TOTAL_MILEAGE,a.OWNER_NAME,b.PURCHASED_DATE,c.WR_LABOURCODE,c.WR_LABOURNAME,d.PART_NO,d.PART_NAME \n" );
		sql.append("from tt_as_repair_order a,tm_vehicle b,tt_as_ro_labour c,tt_as_ro_repair_part d\n" );
		sql.append("where a.id=d.ro_id\n" );
		sql.append("and a.id=c.ro_id\n" );
		sql.append("and a.id=d.ro_id\n" );
		sql.append("and a.vin = b.vin\n");
		sql.append("and d.labour_code = c.wr_labourcode\n");
		//sql.append("and c.labour_code=d.labour_code\n");
		sql.append("and a.vin='"+vin+"'");
		//List<Object> param =new ArrayList<Object>();
		return this.select(LabourPartBean.class, sql.toString(), null);
		//return pageQuery(LabourPartBean.class, sql.toString(), null,
		//		pageSize, curPage);
	}
	/**
	 * 
	* @Title: getFree 
	* @Description: TODO(根据申请的免费保养次数取得免费保养规则表中的数据) 
	* @param @return    设定文件 
	* @return List<TtAsWrQamaintainPO>    返回类型 
	* @throws
	 */
	public List<TtAsWrQamaintainPO> getFree(int times,Long OemCompanyId,int month,int day,Double mileage) {
		StringBuffer sql= new StringBuffer();
		sql.append("select * from TT_AS_WR_QAMAINTAIN\n" );
		sql.append("where 1=1\n" );
		sql.append("and free_times="+times+" \n");
		sql.append("and oem_company_id='"+OemCompanyId+"' \n");
		sql.append(" and MIN_DAYS<="+day+" \n");
		sql.append(" and MAX_DAYS>="+day+" \n");
		//sql.append("and month>="+month+"\n"); //保修月份小于
		//sql.append("and start_mileage<="+mileage+""); //行驶里程数大于开始里程数
		sql.append("and start_mileage<="+mileage+" \n"); //行驶里程数小于结束里程数
		sql.append("and end_mileage>="+mileage+""); //行驶里程数小于结束里程数
		return select(TtAsWrQamaintainPO.class,sql.toString(),null);
		
	}
	public List<TtAsWrQamaintainPO> getFree2(int times,Long OemCompanyId,int month,int day,Double mileage) {
		StringBuffer sql= new StringBuffer();
		sql.append("select * from TT_AS_WR_QAMAINTAIN\n" );
		sql.append("where 1=1\n" );
		sql.append("and free_times="+times+" \n");
		sql.append("and oem_company_id='"+OemCompanyId+"' \n");
		//sql.append(" and MIN_DAYS<="+day+" \n");
		sql.append(" and MAX_DAYS>="+day+" \n");
		//sql.append("and month>="+month+"\n"); //保修月份小于
		//sql.append("and start_mileage<="+mileage+""); //行驶里程数大于开始里程数
		//sql.append("and start_mileage<="+mileage+" \n"); //行驶里程数小于结束里程数
		sql.append("and end_mileage>="+mileage+""); //行驶里程数小于结束里程数
		return select(TtAsWrQamaintainPO.class,sql.toString(),null);
		
	}
	
	/**
	 * 
	* @Title: getActFree 
	* @Description: TODO(取得车辆在服务活动中的保养次数) 
	* @param @param vin
	* @param @return    设定文件 
	* @return Integer    返回类型 
	* @throws
	 */
	public Integer getActFree(String vin) {
		int times  = 0;
		StringBuffer sql= new StringBuffer();
		sql.append("select count(*) as upload_pre_period from TT_AS_ACTIVITY_VEHICLE v\n" );
		sql.append("left outer join tt_as_activity a on v.activity_id = a.activity_id\n" );
		sql.append("where 1=1\n" );
		sql.append("and a.activity_type="+Constant.SERVICEACTIVITY_KIND_02+"\n" );
		sql.append("and v.vin=''");
		
		List<TtAsActivityPO> ls = select(TtAsActivityPO.class,sql.toString(),null);
		if (ls!=null) {
			if (ls.size()>0) {
				times = ls.get(0).getUploadPrePeriod();
			}
		}
		 
		return times;
	}
	
	/**
	 * 
	* @Title: getPartGuaListByVin 
	* @Description: TODO(根据VIN得到配件三包列表) 
	* @param @param vin
	* @param @return    设定文件 
	* @return List    返回类型 
	* @throws
	 */
	public List<TtAsWrRuleListPO> getPartGuaListByVin (String vin,String partCode) {
		StringBuffer sql= new StringBuffer();
		sql.append("select PART_CODE as part_code, max(CLAIM_MONTH) as claim_month,MAX(CLAIM_MELIEAGE) as claim_melieage from tm_vehicle v\n" );
		sql.append("left outer join TT_AS_WR_GAME a  on v.claim_tactics_id=a.id\n" );
		sql.append("left outer join TT_AS_WR_RULE b on a.rule_id = b.id\n" );
		sql.append("left outer join TT_AS_WR_RULE_LIST c on b.id=c.rule_id\n" );
		sql.append("where 1=1\n" );
		sql.append("and V.VIN = '"+vin+"'\n" );
		sql.append("and PART_CODE='"+partCode+"'");
		sql.append("GROUP BY PART_CODE");
		List<TtAsWrRuleListPO> ls = select(TtAsWrRuleListPO.class,sql.toString(),null);
		return ls;
	}
	
	/**
	 * 
	* @Title: getPartGuaCommonListByVin 
	* @Description: TODO(通用配件三包查询) 
	* @param @param partCode
	* @param @return    设定文件 
	* @return List<TtAsWrRuleListPO>    返回类型 
	* @throws
	 */
	public List<TtAsWrRuleListPO> getPartGuaCommonListByVin (String partCode) {
		StringBuffer sql= new StringBuffer();
		sql.append("select PART_CODE as part_code, max(CLAIM_MONTH) as claim_month,MAX(CLAIM_MELIEAGE) as claim_melieage from \n" );
		sql.append(" TT_AS_WR_GAME a \n" );
		sql.append("left outer join TT_AS_WR_RULE b on a.rule_id = b.id\n" );
		sql.append("left outer join TT_AS_WR_RULE_LIST c on b.id=c.rule_id\n" );
		sql.append("where 1=1\n" );
		sql.append("and PART_CODE='"+partCode+"' \n");
		sql.append("and b.rule_code='"+Constant.COMMON_RULE+"' \n");
		sql.append("GROUP BY PART_CODE");
		List<TtAsWrRuleListPO> ls = select(TtAsWrRuleListPO.class,sql.toString(),null);
		return ls;
	}
	/**
	 * 
	* @Title: getOutGua 
	* @Description: TODO(里程数判断之前应该做过第几次保养:根据里程) 
	* @param @param mileage
	* @param @return    设定文件 
	* @return int    返回类型 
	* @throws
	 */
	public int getOutGuaMile(String mileage) {
		StringBuffer sql= new StringBuffer();
		sql.append("select (min(free_times)-1) as free_times From tt_as_wr_qamaintain\n" );
		sql.append("where 1=1\n" );
		sql.append("and end_mileage >= "+mileage+" ");
		List<TtAsWrQamaintainPO> ls = select(TtAsWrQamaintainPO.class,sql.toString(),null);
		int times = 0;
		if (ls!=null&&ls.size()>0) {
		TtAsWrQamaintainPO tawqp = ls.get(0);
		times = tawqp.getFreeTimes();
		}
		return times<0?0:times;
	}
	/**
	 * 
	* @Title: getOutGuaTime 
	* @Description: TODO(里程数判断之前应该做过第几次保养:根据时间) 
	* @param @param duration
	* @param @return    设定文件 
	* @return int    返回类型 
	* @throws
	 */
	public int getOutGuaTime(int duration) {
		StringBuffer sql= new StringBuffer();
		sql.append("select (min(free_times)-1) as free_times From tt_as_wr_qamaintain\n" );
		sql.append("where 1=1\n" );
		sql.append("and max_days >= "+duration+" ");
		List<TtAsWrQamaintainPO> ls = select(TtAsWrQamaintainPO.class,sql.toString(),null);
		int times = 0;
		if (ls!=null&&ls.size()>0) {
		TtAsWrQamaintainPO tawqp = ls.get(0);
		times = tawqp.getFreeTimes();
		}
		return times<0?0:times;
	}

	/*****
	 * 
	 * 根据VIN获取车主姓名电话
	 * @param vin
	 * @return list
	 */
	public  List<Map<String, Object>> getVinUserName(String vin){
		StringBuffer sql = new StringBuffer();
		//sql.append(" select tc.main_phone,tc.ctm_name from tt_customer tc where tc.ctm_id in ( \n");
		//sql.append("select a.ctm_id from tt_dealer_actual_sales a where a.vehicle_id in \n");
		//sql.append("(select c.vehicle_id from tm_vehicle c where c.vin='"+vin+"')) \n");
		
		sql.append("SELECT tc.main_phone, tc.ctm_name,TC.OTHER_PHONE,TC.ADDRESS\n");
		sql.append("  FROM tt_customer tc\n" );
		sql.append("      ,tt_dealer_actual_sales a\n" );
		sql.append("    ,tm_vehicle c\n" );
		sql.append(" where tc.ctm_id=a.ctm_id\n" );
		sql.append("   and a.vehicle_id=c.vehicle_id\n" );
		sql.append("   and c.vin ='"+vin+"'");
		sql.append("  and  A.Is_Return="+Constant.IF_TYPE_NO+" ");

		List<Map<String, Object>> list = pageQuery(sql.toString(), null,getFunName());
		return list;
	}
	//显示送修人信息
	public List<Map<String, Object>> getVinUserName2(String id){
		StringBuffer sql = new StringBuffer();
		sql.append("select o.deliverer ctm_name,o.deliverer_phone||' '||o.deliverer_mobile main_phone from tt_as_wr_application a ,tt_as_repair_order o\n");
		sql.append(" where a.ro_no = o.ro_no\n");  
		sql.append(" and a.id = "+id+"\n");
		List<Map<String, Object>> list = pageQuery(sql.toString(), null,getFunName());
		return list;
	}
	/**********
	 * 
	 * 校验里程
	 * @param vin
	 * @return
	 */
	public  List<Map<String, Object>> getMileage(String vin){
		StringBuffer sql = new StringBuffer();
		sql.append("select v.mileage from tm_vehicle v where v.vin='"+vin+"'");
		List<Map<String, Object>> list = pageQuery(sql.toString(), null,getFunName());
		return list;
	}
	/*******
	 * 根据工单编码查询工单ID
	 * @param vin
	 * @return
	 */
	public  List<Map<String, Object>> getID(String roNo){
		StringBuffer sql = new StringBuffer();
		sql.append("select id from TT_AS_REPAIR_ORDER where ro_no='"+roNo+"'");
		List<Map<String, Object>> list = pageQuery(sql.toString(), null,getFunName());
		return list;
	}
	
	
	
	/******
	 * 更新里程
	 */
	public void updateMileage(TmVehiclePO po,TmVehiclePO po1){
		factory.update(po, po1);
	}
	/*****
	 * 插入预授权申请单
	 * @param po
	 */
	public void insertTtAsWrForeapprovalitemPO(TtAsWrForeapprovalitemPO po){
		factory.insert(po);
	}
	/*****
	 * 插入预授权申请明细
	 * @param po
	 */
	public void insertTtAsWrForeapprovalPO(TtAsWrForeapprovalPO po){
		factory.insert(po);
	}
	/*******
	 * 预授权审核状态更新
	 * @param fid
	 */
	public void updateTtAsWrForeapproval(String fid,String opintion){
		StringBuffer sql = new StringBuffer();
		ActionContext act = ActionContext.getContext();
		AclUserBean logonUser = (AclUserBean) act.getSession().get(
				Constant.LOGON_USER);
		sql.append("update tt_as_wr_foreapproval wf set wf.report_status="+Constant.RO_FORE_02+" ,WF.AUDIT_PERSON="+logonUser.getUserId()+",wf.opinion='"+opintion+"' where id="+fid);
		factory.update(sql.toString(), null);
	}
	/*******
	 * 预授权审核状态更新
	 * @param fid
	 * 添加一个是否同意费用标识后调用的此方法
	 */
	public void updateTtAsWrForeapproval2(String fid,String opintion,Integer accreditAmount){
		StringBuffer sql = new StringBuffer();
		ActionContext act = ActionContext.getContext();
		AclUserBean logonUser = (AclUserBean) act.getSession().get(
				Constant.LOGON_USER);
		sql.append("update tt_as_wr_foreapproval wf set wf.report_status="+Constant.RO_FORE_02+",wf.update_date=sysdate ,WF.AUDIT_PERSON="+logonUser.getUserId()+",wf.accredit_amount="+accreditAmount+",wf.opinion='"+opintion+"' where id="+fid);
		factory.update(sql.toString(), null);
	}
	/**
	 * 预授权审核状态更新
	 * @param id
	 * 更新维修工单中的一个字段,accredit_amount  0/1
	 */
	public void updateTtAsRepairOrder(String fid,Integer accreditAmount,AclUserBean user){
		StringBuilder sql= new StringBuilder("\n");
		sql.append("update tt_as_repair_order ro\n" );
		sql.append("   set ro.accredit_amount = "+accreditAmount+",\n" );
		sql.append("       ro.update_by = "+user.getUserId()+",\n");
		sql.append("       ro.update_date = sysdate\n");
		sql.append(" where ro.ro_no = (select f.ro_no\n" );
		sql.append("                     from tt_as_wr_foreapproval f\n" );
		sql.append("                    where f.id = "+fid+")");
		factory.update(sql.toString(), null);
	}
	public void updateOrder(String id){
		StringBuffer sql = new StringBuffer();
		sql.append("update TT_AS_REPAIR_ORDER ro set ro.forl_status="+Constant.RO_FORE_02+" where id="+id);
		factory.update(sql.toString(), null);
	}
	public void backTtAsWrForeapproval(String fid,String opinion){
		ActionContext act = ActionContext.getContext();
		AclUserBean logonUser = (AclUserBean) act.getSession().get(
				Constant.LOGON_USER);
		StringBuffer sql = new StringBuffer();
		sql.append("update tt_as_wr_foreapproval wf set wf.report_status="+Constant.RO_FORE_03+",wf.update_date = sysdate, wf.AUDIT_PERSON="+logonUser.getUserId()+",wf.opinion='"+opinion+"' where id="+fid);
		factory.update(sql.toString(), null);
	}
	public void backOrder(String id){
		StringBuffer sql = new StringBuffer();
		sql.append("update TT_AS_REPAIR_ORDER ro set ro.forl_status="+Constant.RO_FORE_03+" where id="+id);
		factory.update(sql.toString(), null);
	}
	public void backOrderRono(String roNo){
		StringBuffer sql = new StringBuffer();
		sql.append("update TT_AS_REPAIR_ORDER ro set ro.forl_status="+Constant.RO_FORE_01+" where ro_no='"+roNo+"'");
		factory.update(sql.toString(), null);
	}
	public  List<Map<String, Object>> getGuranteeCode(String vin){
		StringBuffer sql = new StringBuffer();
		sql.append("select * from tt_as_wr_rule where id=(SELECT rule_id FROM Tt_As_Wr_Game where\n");
		sql.append(" id=(SELECT v.claim_tactics_id FROM TM_VEHICLE v\n" );
		sql.append(" where v.vin='"+vin+"'))");
		List<Map<String, Object>> list = pageQuery(sql.toString(), null,getFunName());
		return list;
	}
	/***
	 * 根据工单ID 查询出预授权申请单的审核内容和预授权内容
	 */
	public  List<Map<String, Object>> getOpinion(String fid){
		StringBuffer sql = new StringBuffer();
		sql.append("select wf.opinion,wf.remark from TT_AS_WR_FOREAPPROVAL wf where WF.id="+fid);
		List<Map<String, Object>> list = pageQuery(sql.toString(), null,getFunName());
		return list;
	}//  select * from TT_AS_WR_APPLICATION where ro_no='N8980100RO090100291'
	public  List<Map<String, Object>> getApplication(String roNo){
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from TT_AS_WR_APPLICATION where ro_no='"+roNo+"'");
		List<Map<String, Object>> list = pageQuery(sql.toString(), null,getFunName());
		return list;
	}
	  /**
	   * 维修历史记录
	   * @param vin 车辆VIN码
	   * @param claimTypes 索赔单类型集合（如：TYPE1,TYPE2...）
	   */
	  public List<Map<String,Object>> maintaimHistory(String vin,String claimTypes){ //YH 2010.11.29
	    
	    StringBuilder sql= new StringBuilder();
	    sql.append("WITH t1 As (Select * From TT_AS_WR_APPLICATION a1 Where A1.STATUS IN (10791004,10791005,10791006,10791007,10791008)\n");
	    sql.append("AND A1.CLAIM_TYPE IN (10661001,10661007,10661009)\n" );
	    sql.append("AND A1.VIN = ?)\n" );
	    sql.append(",TMP_WRLABINFO AS\n" );
	    sql.append("(select distinct wr_labourcode, wr_labourname,ID from TT_AS_WR_LABOURITEM t2 Where Exists (Select 1 From t1 Where t1.Id  =t2.Id ))\n" );
	    sql.append("SELECT A.AUDITING_DATE,A.ID,A.CLAIM_NO,A.CLAIM_TYPE,TO_CHAR(A.RO_STARTDATE,'YYYY-MM-DD') RO_STARTDATE, A.IN_MILEAGE,A.STATUS\n" );
	    sql.append(",D.DEALER_ID,D.DEALER_CODE,D.DEALER_NAME\n" );
	    sql.append(",WW.WR_LABOURCODE,WW.WR_LABOURNAME\n" );
	    sql.append(",B.PART_CODE,B.PART_NAME,B.IS_AGREE\n" );
	    sql.append(",E.NAME\n" );
	    sql.append("FROM t1 A\n" );
	    sql.append(",TT_AS_WR_PARTSITEM B\n" );
	    sql.append(",TMP_WRLABINFO WW\n" );
	    sql.append(",TM_DEALER D\n" );
	    sql.append(",TC_USER E\n" );
	    sql.append(",TT_AS_WR_APPAUTHITEM F\n" );
	    sql.append("WHERE 1=1\n" );
	    sql.append("and f.approval_date in (select max(wa.approval_date) from tt_as_wr_appauthitem wa where wa.id=a.id and wa.approval_result in (10791004,10791005,10791006) and wa.approval_person not like '%结算审核%' group by wa.id)\n");
	    sql.append("AND A.ID = B.ID\n" );
	    sql.append("AND B.WR_LABOURCODE = WW.wr_labourcode\n" );
	    sql.append("AND A.ID=F.ID AND A.ID = WW.ID \n" );
	    sql.append("AND F.CREATE_BY = E.USER_ID(+)\n" );
	    sql.append("AND A.DEALER_ID = D.DEALER_ID(+)\n" );
	    sql.append("AND F.APPROVAL_RESULT  in (10791004,10791005,10791006)\n" );
	    sql.append("ORDER BY A.RO_STARTDATE DESC,A.ID DESC");
	    List<Object> paramList = new ArrayList<Object>();
	    paramList.add(CommonUtils.checkNull(vin));
	    
	    return this.pageQuery(sql.toString(), paramList, this.getFunName());
	  }
	  
     /**
      * 授权历史记录
      * @param vin 车辆VIN码
      * @param expStatus 不统计的索赔单状态集合（如：STATUS1,STATUS2...）
      */
	     public List<Map<String,Object>> auditingHistory(String vin,String expStatus){
	         
	    	 StringBuffer sql= new StringBuffer();
	    	 sql.append("select * from (SELECT A.ID,\n" );
	    	 sql.append("       A.CLAIM_NO,\n" );
	    	 sql.append("       D.DEALER_CODE,\n" );
	    	 sql.append("       D.DEALER_NAME,\n" );
	    	 sql.append("       E.NAME AUTH_NAME,\n" );
	    	 sql.append("       TO_CHAR(F.approval_date, 'YYYY-MM-DD') AUTH_DATE,\n" );
	    	 sql.append("       A.STATUS,\n" );
	    	 sql.append("       F.REMARK,\n" );
	    	 sql.append("       C.WR_LABOURCODE,\n" );
	    	 sql.append("       C.WR_LABOURNAME,\n" );
	    	 sql.append("       B.PART_CODE,\n" );
	    	 sql.append("       B.PART_NAME,\n" );
	    	 sql.append("       A.CLAIM_TYPE,\n" );
	    	 sql.append("       B.IS_AGREE,\n" );
	    	 sql.append("       f.approval_level_code,\n" );
	    	 sql.append("       f.approval_person\n" );
	    	 sql.append("  FROM TT_AS_WR_APPLICATION A,\n" );
	    	 sql.append("       TT_AS_WR_PARTSITEM   B,\n" );
	    	 sql.append("       TT_AS_WR_LABOURITEM  C,\n" );
	    	 sql.append("       TM_DEALER            D,\n" );
	    	 sql.append("       TC_USER              E,\n" );
	    	 sql.append("       TT_AS_WR_APPAUTHITEM F\n" );
	    	 sql.append(" WHERE 1 = 1\n" );
	    	 sql.append("   AND A.ID = B.ID\n" );
	    	 sql.append("   AND A.ID = C.ID\n" );
	    	 sql.append("   AND A.ID = F.ID\n" );
	    	 sql.append("   AND A.DEALER_ID = D.DEALER_ID(+)\n" );
	    	 sql.append("   AND F.CREATE_BY = E.USER_ID\n" );
	    	 sql.append("   AND B.WR_LABOURCODE = C.WR_LABOURCODE\n" );
	    	 sql.append("AND A.STATUS NOT IN (10791001)\n" );
	    	 sql.append("   AND f.Approval_Person like '%授权审核%'\n" );
	    	 sql.append("   AND F.APPROVAL_RESULT >= 10791004\n" );
	    	 sql.append("   AND A.VIN = ?\n" );
	    	 sql.append("   and f.approval_level_code != '100'\n" );
	    	 sql.append("\n" );
	    	 sql.append("\n" );
	    	 sql.append(" union all\n" );
	    	 sql.append(" SELECT A.ID,\n" );
	    	 sql.append("       A.CLAIM_NO,\n" );
	    	 sql.append("       D.DEALER_CODE,\n" );
	    	 sql.append("       D.DEALER_NAME,\n" );
	    	 sql.append("       E.NAME AUTH_NAME,\n" );
	    	 sql.append("       TO_CHAR(F.approval_date, 'YYYY-MM-DD') AUTH_DATE,\n" );
	    	 sql.append("       A.STATUS,\n" );
	    	 sql.append("       F.REMARK,\n" );
	    	 sql.append("       C.WR_LABOURCODE,\n" );
	    	 sql.append("       C.WR_LABOURNAME,\n" );
	    	 sql.append("       B.PART_CODE,\n" );
	    	 sql.append("       B.PART_NAME,\n" );
	    	 sql.append("       A.CLAIM_TYPE,\n" );
	    	 sql.append("       B.IS_AGREE,\n" );
	    	 sql.append("       f.approval_level_code,\n" );
	    	 sql.append("       f.approval_person\n" );
	    	 sql.append("  FROM TT_AS_WR_APPLICATION_BACKUP A,\n" );
	    	 sql.append("       TT_AS_WR_PARTSITEM   B,\n" );
	    	 sql.append("       TT_AS_WR_LABOURITEM  C,\n" );
	    	 sql.append("       TM_DEALER            D,\n" );
	    	 sql.append("       TC_USER              E,\n" );
	    	 sql.append("       TT_AS_WR_APPAUTHITEM F\n" );
	    	 sql.append(" WHERE 1 = 1\n" );
	    	 sql.append("   AND A.ID = B.ID\n" );
	    	 sql.append("   AND A.ID = C.ID\n" );
	    	 sql.append("   AND A.ID = F.ID\n" );
	    	 sql.append("   AND A.DEALER_ID = D.DEALER_ID(+)\n" );
	    	 sql.append("   AND F.CREATE_BY = E.USER_ID\n" );
	    	 sql.append("   AND B.WR_LABOURCODE = C.WR_LABOURCODE\n" );
	    	 sql.append("AND A.STATUS NOT IN (10791001)\n" );
	    	 sql.append("   AND f.Approval_Person like '%授权审核%'\n" );
	    	 sql.append("   AND F.APPROVAL_RESULT >= 10791004\n" );
	    	 sql.append("   AND A.VIN = ?\n" );
	    	 sql.append("   and f.approval_level_code != '100'\n" );
	    	 sql.append(") order by id desc");


	        List<Object> paramList = new ArrayList<Object>();
	        paramList.add(CommonUtils.checkNull(vin));
	        paramList.add(CommonUtils.checkNull(vin));
	      
	        return this.pageQuery(sql.toString(), paramList, this.getFunName());
	     }
	  
	  /**
	   * 车型免费保养历史记录
	   * 注意：1、现在按时间顺序做取巧，通过工单创建的时间排序，取得序号做为保养次数
	   *       2、工单类型：保养
	   *          当工单做过索赔则使用索赔单的结算金额做保养费用，
	   *          如果工单未做过索赔则费用显示为"自费"
	   *       3、工单类型：服务活动（保养）
	   *          使用服务活动金额做为保养金额
	   * @param vin 车辆VIN码
	   */
	  public List<Map<String,Object>> freeMaintainHistory(String vin){
		  StringBuffer sql= new StringBuffer();
		  sql.append("SELECT ROWNUM NUM, A1.*\n" );
		  sql.append("  FROM (SELECT *\n" );
		  sql.append("          FROM (SELECT A.RO_NO,\n" );
		  sql.append("                       a.repair_type_code,\n" );
		  sql.append("                       a.order_valuable_type,\n" );
		  sql.append("                       TO_CHAR(A.RO_CREATE_DATE, 'YYYY-MM-DD') RO_CREATE_DATE,\n" );
		  sql.append("                       C.DEALER_NAME,\n" );
		  sql.append("                       C.DEALER_CODE,\n" );
		  sql.append("                       a.ro_status,\n" );
		  sql.append("                       d.report_status,\n" );
		  sql.append("                       A.IN_MILEAGE TOTAL_MILEAGE,\n" );
		  sql.append("                       NVL(B.BALANCE_AMOUNT, 0) FREEAMOUNT,\n" );
		  sql.append("                       NVL2(B.ID, '1', '0') ISFREE,\n" );
		  sql.append("                       A.CREATE_DATE,\n" );
		  sql.append("                       A.Free_Times,\n" );
		  sql.append("                       vc.apply_type CHANAGE_TYPE,\n" );
		  sql.append("                       0 DISTORY\n" );
		  sql.append("                  FROM TT_AS_REPAIR_ORDER    A,\n" );
		  sql.append("                       TT_AS_WR_APPLICATION  B,\n" );
		  sql.append("                       TM_DEALER             C,\n" );
		  sql.append("                       Tt_As_Wr_Foreapproval d,\n" );
		  sql.append("                       TT_VEHICLE_CHANGE     vc\n" );
		  sql.append("                 WHERE A.RO_NO = B.RO_NO(+)\n" );
		  sql.append("                   and a.ro_no = d.ro_no(+)\n" );
		  sql.append("                   AND A.DEALER_CODE = C.DEALER_CODE(+)\n" );
		  sql.append("                   AND A.REPAIR_TYPE_CODE = '11441004'\n" );
		  sql.append("                   AND ((A.APPROVAL_YN = 1 AND A.FORL_STATUS = 11561002) OR\n" );
		  sql.append("                       A.APPROVAL_YN = 0 OR A.APPROVAL_YN IS NULL)\n" );
		  sql.append("                   and a.ro_no=vc.error_ro_code(+)\n" );
		  sql.append("                   AND A.VIN = ?\n" );
		  sql.append("                UNION ALL\n" );
		  sql.append("                SELECT A.RO_NO,\n" );
		  sql.append("                       a.repair_type_code,\n" );
		  sql.append("                       a.order_valuable_type,\n" );
		  sql.append("                       TO_CHAR(A.RO_CREATE_DATE, 'YYYY-MM-DD') RO_CREATE_DATE,\n" );
		  sql.append("                       C.DEALER_NAME,\n" );
		  sql.append("                       C.DEALER_CODE,\n" );
		  sql.append("                       b.status,\n" );
		  sql.append("                       d.report_status,\n" );
		  sql.append("                       A.IN_MILEAGE TOTAL_MILEAGE,\n" );
		  sql.append("                       NVL(B.ACTIVITY_FEE, 0) FREEAMOUNT,\n" );
		  sql.append("                       '1' ISFREE,\n" );
		  sql.append("                       A.CREATE_DATE,\n" );
		  sql.append("                       A.Free_Times,\n" );
		  sql.append("                      vc.apply_type CHANAGE_TYPE,\n" );
		  sql.append("                       0 DISTORY\n" );
		  sql.append("                  FROM TT_AS_REPAIR_ORDER    A,\n" );
		  sql.append("                       TT_AS_ACTIVITY        B,\n" );
		  sql.append("                       TM_DEALER             C,\n" );
		  sql.append("                       Tt_As_Wr_Foreapproval d,\n" );
		  sql.append("                       TT_VEHICLE_CHANGE     vc\n" );
		  sql.append("                 WHERE A.CAM_CODE = B.ACTIVITY_CODE(+)\n" );
		  sql.append("                   and a.ro_no = d.ro_no(+)\n" );
		  sql.append("                   AND A.DEALER_CODE = C.DEALER_CODE(+)\n" );
		  sql.append("                   AND A.REPAIR_TYPE_CODE = '11441005'\n" );
		  sql.append("                      and a.ro_no=vc.error_ro_code(+)\n" );
		  sql.append("                   AND A.VIN = ? \n" );
		  sql.append("                union all\n" );
		  sql.append("                SELECT A.RO_NO,\n" );
		  sql.append("                       a.repair_type_code,\n" );
		  sql.append("                       a.order_valuable_type,\n" );
		  sql.append("                       TO_CHAR(A.RO_CREATE_DATE, 'YYYY-MM-DD') RO_CREATE_DATE,\n" );
		  sql.append("                       C.DEALER_NAME,\n" );
		  sql.append("                       C.DEALER_CODE,\n" );
		  sql.append("                       b.status,\n" );
		  sql.append("                       d.report_status,\n" );
		  sql.append("                       A.IN_MILEAGE TOTAL_MILEAGE,\n" );
		  sql.append("                       NVL(B.BALANCE_AMOUNT, 0) FREEAMOUNT,\n" );
		  sql.append("                       NVL2(B.ID, '1', '0') ISFREE,\n" );
		  sql.append("                       A.CREATE_DATE,\n" );
		  sql.append("                       A.Free_Times,\n" );
		  sql.append("                      vc.apply_type CHANAGE_TYPE,\n" );
		  sql.append("                       1 DISTORY\n" );
		  sql.append("                  FROM Tt_As_Repair_Order_Backup A,\n" );
		  sql.append("                       TT_AS_WR_APPLICATION      B,\n" );
		  sql.append("                       TM_DEALER                 C,\n" );
		  sql.append("                       Tt_As_Wr_Foreapproval     d,\n" );
		  sql.append("                       TT_VEHICLE_CHANGE     vc\n" );
		  sql.append("                 WHERE A.RO_NO = B.RO_NO(+)\n" );
		  sql.append("                   and a.ro_no = d.ro_no(+)\n" );
		  sql.append("                   AND A.DEALER_CODE = C.DEALER_CODE(+)\n" );
		  sql.append("                   AND A.REPAIR_TYPE_CODE = '11441004'\n" );
		  sql.append("                   AND ((A.APPROVAL_YN = 1 AND A.FORL_STATUS = 11561002) OR\n" );
		  sql.append("                       A.APPROVAL_YN = 0 OR A.APPROVAL_YN IS NULL)\n" );
		  sql.append("                     and a.ro_no=vc.error_ro_code(+)\n" );
		  sql.append("                   AND A.VIN = ? \n" );
		  sql.append("                UNION ALL\n" );
		  sql.append("                SELECT A.RO_NO,\n" );
		  sql.append("                       a.repair_type_code,\n" );
		  sql.append("                       a.order_valuable_type,\n" );
		  sql.append("                       TO_CHAR(A.RO_CREATE_DATE, 'YYYY-MM-DD') RO_CREATE_DATE,\n" );
		  sql.append("                       C.DEALER_NAME,\n" );
		  sql.append("                       C.DEALER_CODE,\n" );
		  sql.append("                       b.status,\n" );
		  sql.append("                       d.report_status,\n" );
		  sql.append("                       A.IN_MILEAGE TOTAL_MILEAGE,\n" );
		  sql.append("                       NVL(B.ACTIVITY_FEE, 0) FREEAMOUNT,\n" );
		  sql.append("                       '1' ISFREE,\n" );
		  sql.append("                       A.CREATE_DATE,\n" );
		  sql.append("                       A.Free_Times,\n" );
		  sql.append("                      vc.apply_type CHANAGE_TYPE,\n" );
		  sql.append("                       1 DISTORY\n" );
		  sql.append("                  FROM Tt_As_Repair_Order_Backup A,\n" );
		  sql.append("                       TT_AS_ACTIVITY            B,\n" );
		  sql.append("                       TM_DEALER                 C,\n" );
		  sql.append("                       Tt_As_Wr_Foreapproval     d,\n" );
		  sql.append("                        TT_VEHICLE_CHANGE     vc\n" );
		  sql.append("                 WHERE A.CAM_CODE = B.ACTIVITY_CODE(+)\n" );
		  sql.append("                   and a.ro_no = d.ro_no(+)\n" );
		  sql.append("                   AND A.DEALER_CODE = C.DEALER_CODE(+)\n" );
		  sql.append("                   AND A.REPAIR_TYPE_CODE = '11441005'\n" );
		  sql.append("                       and a.ro_no=vc.error_ro_code(+)\n" );
		  sql.append("                   AND A.VIN = ? )\n" );
		  sql.append("         ORDER BY free_times, total_mileage asc) A1");


		  List<Object> paramList = new ArrayList<Object>();
		  paramList.add(vin);
		  paramList.add(vin);
		  paramList.add(vin);
		  paramList.add(vin);
		  
		  return this.pageQuery(sql.toString(), paramList, this.getFunName());
	  }
	  /**
	   * 
	  * @Title: getPartAddPer 
	  * @Description: TODO(取配件加价率) 
	  * @param @param dealerId
	  * @param @return    设定文件 
	  * @return String    返回类型 
	  * @throws
	   */
	  public String getPartAddPer(String dealerId) {
			  StringBuffer sql= new StringBuffer();
			  sql.append("select parameter_value  from tm_down_parameter\n" );
			  sql.append("where 1=1\n" );
			  sql.append("and parameter_code="+Constant.CLAIM_BASIC_PARAMETER_09+"\n" );
			  sql.append("and dealer_id="+dealerId+"");
			  TmDownParameterPO tdp = new TmDownParameterPO();
			  List<TmDownParameterPO> ls = dao.select(TmDownParameterPO.class,sql.toString(), null);
			  if (ls!=null) {
				  if (ls.size()>0) {
					  tdp = ls.get(0);
				  }
			  }
			  return tdp.getParameterValue();
	  }
	  
	  /**
	   * 
	  * @Title: getPartPrice 
	  * @Description: TODO(通过partCode,supplierCode 取得配件) 
	  * @param @return    设定文件 
	  * @return TmPtPartBasePO    返回类型 
	  * @throws
	   */
	  public TmPtPartBasePO getPartPrice(Map<String,String> map) {
		  StringBuffer sql= new StringBuffer();
		  sql.append("select claim_price from TM_PT_PART_BASE a\n" );
		  sql.append("left outer join TM_PT_PART_SUP_RELATION b on a.part_id=b.order_id\n" );
		  sql.append("left outer join TM_PT_SUPPLIER c on b.supplier_id=c.supplier_id\n" );
		  sql.append("where 1=1\n" );
		  sql.append("and part_code='"+map.get("partCode")+"'\n" );
		  if (Utility.testString(map.get("supplierCode"))) {
			  sql.append("and supplier_code='"+map.get("supplierCode")+"'");
		  }
		  TmPtPartBasePO tp = new TmPtPartBasePO();
		  List<TmPtPartBasePO> ls = select(TmPtPartBasePO.class,sql.toString(),null);
		  if (ls!=null) {
			  if (ls.size()>0) {
				  tp = ls.get(0);
			  }
		  }
		  return tp;
	  }
	  
	  /**
	   * 查询对应索赔单要打印配件明细
	   * @param claimId 索赔单ID
	   * @return
	   */
	  public List<Map<String, Object>> queryClaimPart(Long claimId){
		  StringBuilder sql= new StringBuilder();
		  
		  TcCodePO po = new TcCodePO();
		  po.setType(Constant.chana+"");
		  TcCodePO poValue = (TcCodePO)this.select(po).get(0);
		  String codeId = poValue.getCodeId();
		  if(codeId==null){
			  codeId="0";
		  }
		  if(Integer.parseInt(codeId)==Constant.chana_jc){
			  sql.append("SELECT B.CLAIM_NO, B.VIN, B.YIELDLY, B.MODEL_CODE, B.ENGINE_NO, B.IN_MILEAGE, B.TROUBLE_DESC,\n" );
			  sql.append("       C.QUANTITY BALANCE_QUANTITY,C.DOWN_PART_CODE, C.DOWN_PART_NAME, T.TROUBLE_CODE_NAME AS REMARK, TO_CHAR(D.PURCHASED_DATE, 'YYYY-MM-DD') PURCHASED_DATE,\n" );
			  sql.append("       E.DEALER_CODE, E.DEALER_NAME, F.DELIVERER, F.DELIVERER_PHONE, C.DOWN_PRODUCT_NAME DC_NAME,\n" );
			  sql.append("       to_char(B.AUDITING_DATE,'yyyy-MM-dd') AUDITING_DATE,T.trouble_type\n");
			  sql.append("FROM TT_AS_WR_APPLICATION B, TT_AS_WR_PARTSITEM C,\n" );
			  sql.append("TM_VEHICLE D, TM_DEALER E, TT_AS_REPAIR_ORDER F,\n" );
			  sql.append("TM_PT_PART_BASE G,Tt_As_Wr_Labouritem  T\n" );
			  sql.append("WHERE 1=1\n" );
			  sql.append("  AND B.ID = C.ID AND T.ID=B.ID and c.wr_labourcode = t.wr_labourcode\n" );
			  sql.append("  AND B.DEALER_ID = E.DEALER_ID\n" ); 
			  sql.append("  AND B.RO_NO = F.RO_NO\n" );
			  sql.append("  AND B.VIN = D.VIN\n" );
			  sql.append("  AND C.PART_CODE = G.PART_CODE(+)\n" );
			  sql.append("  AND g.is_return=1 \n");
			  sql.append("  AND B.ID = ?");
		  }else{
			  sql.append("SELECT B.CLAIM_NO, B.VIN, B.YIELDLY, B.MODEL_CODE, B.ENGINE_NO, B.IN_MILEAGE, B.TROUBLE_DESC,\n" );
			  sql.append("       C.QUANTITY BALANCE_QUANTITY,C.DOWN_PART_CODE, C.DOWN_PART_NAME, C.REMARK, TO_CHAR(D.PURCHASED_DATE, 'YYYY-MM-DD') PURCHASED_DATE,\n" );
			  sql.append("       E.DEALER_CODE, E.DEALER_NAME, F.DELIVERER, F.DELIVERER_PHONE, C.DOWN_PRODUCT_NAME DC_NAME,\n" );
			  sql.append("       to_char(B.AUDITING_DATE,'yyyy-MM-dd') AUDITING_DATE\n");
			  sql.append("FROM TT_AS_WR_APPLICATION B, TT_AS_WR_PARTSITEM C,\n" );
			  sql.append("TM_VEHICLE D, TM_DEALER E, TT_AS_REPAIR_ORDER F,\n" );
			  sql.append("TM_PT_PART_BASE G\n" );
			  sql.append("WHERE 1=1\n" );
			  sql.append("  AND B.ID = C.ID\n" );
			  sql.append("  AND B.DEALER_ID = E.DEALER_ID\n" ); 
			  sql.append("  AND B.RO_NO = F.RO_NO\n" );
			  sql.append("  AND B.VIN = D.VIN\n" );
			  sql.append("  AND C.PART_CODE = G.PART_CODE(+)\n" );
			  sql.append("  AND g.is_return=1 \n");
			  sql.append("  AND B.ID = ?");
		  }
		  
		  List<Object> paramList = new ArrayList<Object>();
		  paramList.add(claimId);
		  
		  return this.pageQuery(sql.toString(), paramList, this.getFunName());
	  }
	  //查询系统保养次数
	  public List<Map<String, Object>> viewFreeTime1(String vin){
		  StringBuilder sql= new StringBuilder();
		  sql.append("select tv.free_times,tv.mileage from tm_vehicle tv where tv.vin='"+vin+"'");
		  return this.pageQuery(sql.toString(), null, this.getFunName());
	  }
	  //查询工单的保养次数
	  public List<Map<String, Object>> viewFreeTimeDoNo(String ro_no){
		  StringBuilder sql= new StringBuilder();
		  sql.append("select p.* from tt_as_repair_order p where p.ro_no='"+ro_no+"'");
		  return this.pageQuery(sql.toString(), null, this.getFunName());
	  }
	  //查询工单信息
	  public List<Map<String, Object>> viewOrderReapir(String id){
		  StringBuilder sql= new StringBuilder();
		  sql.append("select p.* from tt_as_repair_order p where p.id="+id+"");
		  return this.pageQuery(sql.toString(), null, this.getFunName());
	  }
	  public List<TtDealerActualSalesPO> checkUnSaleVechile(String vin){
		  StringBuilder sql= new StringBuilder();
		  sql.append("SELECT A.*\n" );
		  sql.append("FROM TT_DEALER_ACTUAL_SALES A,TM_VEHICLE B\n" );
		  sql.append("WHERE 1=1\n" );
		  sql.append("AND A.VEHICLE_ID = B.VEHICLE_ID\n" );
		  sql.append("AND B.VIN = ?");
		  
		  List<Object> param = new ArrayList<Object>();
		  param.add(CommonUtils.checkNull(vin));

		  return this.select(TtDealerActualSalesPO.class, sql.toString(), param);
	  }
	  public List<Map<String, Object>> viewOutRepair(String ro_no){
		  StringBuilder sql= new StringBuilder();
		  sql.append("select p.* from   tt_as_wr_outrepair p where p.ro_no='"+ro_no+"'");
		  System.out.println("----------------------"+sql.toString());
		  return this.pageQuery(sql.toString(), null, this.getFunName());
	  }
	  
	  
	  
	  public List<Map<String,Object>> getMaintainStateSet(String vin){
		  StringBuilder sql= new StringBuilder("\n");
		  sql.append("select distinct tawq.start_mileage,\n");
		  sql.append("                tawq.end_mileage,\n");  
		  sql.append("                tawq.min_days,\n");  
		  sql.append("                tawq.max_days,\n");  
		  sql.append("                tv.vin,\n");  
		  sql.append("                tv.license_no,\n");  
		  sql.append("                tv.yieldly,\n");  
		  sql.append("                tv.engine_no,\n");  
		  sql.append("                tv.life_cycle,\n");  
		  sql.append("                nvl(tv.free_times,0) free_times,\n");  
		  sql.append("                tawg.game_code,\n");  
		  sql.append("                tawg.game_name,\n");  
		  sql.append("                vmg.MODEL_CODE,\n");  
		  sql.append("                vmg.MODEL_NAME,\n");  
		  sql.append("                g.wrgroup_code,\n");  
		  sql.append("                g.wrgroup_name,\n");  
		  sql.append("                to_char(tv.purchased_date,'yyyy-MM-dd') sales_date\n");  
		  sql.append("  from tt_as_wr_qamaintain    tawq,\n");  
		  sql.append("       tm_vehicle             tv,\n");  
		  sql.append("       tt_as_wr_game          tawg,\n");  
		  sql.append("       tt_dealer_actual_sales tdas,\n");  
		  sql.append("       tt_as_wr_model_group   g,\n");  
		  sql.append("       tt_as_wr_model_item    i,\n");  
		  sql.append("       vw_material_group      vmg\n");  
		  sql.append(" where tv.claim_tactics_id = tawg.id(+)\n");  
		  sql.append("   and i.model_id = tv.model_id\n");  
		  sql.append("   and vmg.MODEL_ID = tv.model_id\n");  
		  sql.append("   and i.wrgroup_id = g.wrgroup_id\n");  
		  sql.append("   and tv.vehicle_id = tdas.vehicle_id(+)\n");  
		  sql.append("   and g.wrgroup_type = ").append(Constant.WR_MODEL_GROUP_TYPE_01).append("\n");  
		  sql.append("   and tawq.free_times = (nvl(tv.free_times,0) + 1)\n");  
		  sql.append("   and tv.vin = '").append(vin).append("'\n");

		  return this.pageQuery(sql.toString(), null, this.getFunName());
	  }
	  
	  public List<NewPartBean> getNewPartBean(String code,String vin){
		  StringBuilder sql= new StringBuilder("\n");
		  sql.append("select t.*,pt.parttype_code type_code,pt.parttype_name type_name, l.claim_month, l.claim_melieage\n");
		  sql.append("    from tm_pt_part_base t,tm_pt_part_type pt, TT_AS_WR_RULE_LIST l,tt_as_wr_game g,tm_vehicle v\n");  
		  sql.append("   where t.part_code = l.part_code\n");
		  sql.append("     and t.part_type_id = pt.id(+)\n");
		  sql.append("     and v.vin = '"+vin+"'\n");  
		  sql.append("     and v.claim_tactics_id = g.id\n");  
		  sql.append("     and g.rule_id = l.rule_id\n");  
		  sql.append("     and t.part_code ='"+code+"'\n");
		  return this.select(NewPartBean.class, sql.toString(), null);
	  }
	  
	  public List<NewPartBean> getNewPartBean2(String code){
		  StringBuilder sql= new StringBuilder("\n");
		  sql.append("select l.part_code,\n");
		  sql.append("       l.part_name,\n");  
		  sql.append("       l.claim_month,\n");  
		  sql.append("       l.claim_melieage,\n");  
		  sql.append("       l.rule_id,\n");  
		  sql.append("       t.parttype_name type_name,\n");  
		  sql.append("       t.parttype_code type_code\n");  
		  sql.append("  from tt_as_wr_rule_list l, tt_as_wr_RULE R ,tm_pt_part_base b,tm_pt_part_type t,tc_code c\n");  
		  sql.append(" where b.part_code = l.part_code\n");  
		  sql.append("   and b.part_type_id = t.id(+)\n");  
		  sql.append("   AND C.CODE_DESC = R.RULE_CODE\n");  
		  sql.append("   and l.rule_id = R.ID\n");  
		  sql.append("   and l.part_code = '"+code+"'\n");  
		  sql.append("   and c.code_id = 13811001\n");
		  return this.select(NewPartBean.class, sql.toString(), null);
	  }
	  
    /**
     * 查询结算单使用
     * ADD 2010-10-24  只为工单查询使用
     * @param map 查询条件
     * @param pageSize 
     * @param curPage
     * @return PageResult<TtAsRepairOrderExtPO>
     */
	public PageResult<TtAsRepairOrderExtPO> queryRepairOrderNew(
			Map<String, String> map, int pageSize, int curPage) {
		StringBuffer sqlStr= new StringBuffer();
		sqlStr.append("select (select count(1) from tt_as_wr_application where ro_no=r.ro_no) as in_claim,r.*\n" );
//		sqlStr.append(" (select y.PART_TYPE_ID from tt_as_ro_repair_part x,Tm_Pt_Part_Base y where x.RO_ID=r.ID AND x.PART_NO=y.PART_CODEE) as PART_TYPE_ID" );
        sqlStr.append(" from tt_as_repair_order r\n" );
		sqlStr.append("left outer join tm_dealer d on r.dealer_code=d.dealer_code \n");
		sqlStr.append(" where 1=1");
		if(Utility.testString(map.get("dealerId"))) {
			sqlStr.append(" and d.DEALER_ID="+map.get("dealerId")+" \n");
		}
		if(Utility.testString(map.get("roNo"))) {
			sqlStr.append(" and r.RO_NO LIKE '%"+map.get("roNo")+"%' \n");
		}
		if(Utility.testString(map.get("repairType"))) {
			sqlStr.append(" and r.REPAIR_TYPE_CODE = '"+map.get("repairType")+"' \n");
		}
		if(Utility.testString(map.get("roCreateDate"))) {
			sqlStr.append(" and r.RO_CREATE_DATE >= to_date('"+map.get("roCreateDate")+"', 'yyyy-mm-dd hh24:mi:ss')\n");
		}
		if(Utility.testString(map.get("deliveryDate"))) {
			sqlStr.append(" AND r.DELIVERY_DATE <= to_date('"+map.get("deliveryDate")+" 23:59:59', 'yyyy-mm-dd hh24:mi:ss') \n");
		}
		//结算开始时间
		if(Utility.testString(map.get("balanceDateStart"))) {
			sqlStr.append(" and r.UPDATE_DATE >= to_date('"+map.get("balanceDateStart")+"', 'yyyy-mm-dd hh24:mi:ss')\n");
		}
		//结算结束时间
		if(Utility.testString(map.get("balanceDateEnd"))) {
			sqlStr.append(" AND r.UPDATE_DATE <= to_date('"+map.get("balanceDateEnd")+" 23:59:59', 'yyyy-mm-dd hh24:mi:ss') \n");
		}
		if (Utility.testString(map.get("customerName"))) {
			sqlStr.append(" and r.owner_name like '%"+map.get("customerName")+"%' \n");
		}
		if(Utility.testString(map.get("isForl"))) {
			sqlStr.append(" and r.FORL_STATUS = '"+map.get("isForl")+"' \n");
		}
		if(Utility.testString(map.get("roStatus"))) {
			sqlStr.append(" and r.RO_STATUS = '"+map.get("roStatus")+"' \n");
		}
		// 车辆VIN码
		if (Utility.testString(map.get("vin"))) {// VIN不为空
			sqlStr.append(GetVinUtil.getVins(map.get("vin").replaceAll("'",
					"\''"), "r"));
		}
		if (Utility.testString(map.get("createDateStr"))) {
			sqlStr.append(" and r.CREATE_DATE >= to_date('"+map.get("createDateStr")+"', 'yyyy-mm-dd hh24:mi:ss')\n");
		}
		if (Utility.testString(map.get("createDateEnd"))) {
			sqlStr.append(" AND r.CREATE_DATE <= to_date('"+map.get("createDateEnd")+" 23:59:59', 'yyyy-mm-dd hh24:mi:ss') \n");
		}
		sqlStr.append("  order by r.ID DESC ");
		return pageQuery(TtAsRepairOrderExtPO.class, sqlStr.toString(), null,
				pageSize, curPage);
	}
	
	/**
	 * Iverson add with 2010-11-15
     * 查询可删除的工单
     * @param map 查询条件
     * @param pageSize 
     * @param curPage
     * @return PageResult<TtAsRepairOrderExtPO>
     */
	public PageResult<TtAsRepairOrderExtPO> queryDeleteRepairOrderNew(
			Map<String, String> map, int pageSize, int curPage) {
		StringBuffer sqlStr= new StringBuffer();
		sqlStr.append("select (select count(1) from tt_as_wr_application where ro_no=r.ro_no) as in_claim,r.*\n" );
        sqlStr.append(" from tt_as_repair_order r\n" );
		sqlStr.append("left outer join tm_dealer d on r.dealer_code=d.dealer_code \n");
		sqlStr.append(" where 1=1 and r.ORDER_VALUABLE_TYPE='"+Constant.RO_PRO_STATUS_02+"' ");
		if(Utility.testString(map.get("dealerId"))) {
			sqlStr.append(" and d.DEALER_ID="+map.get("dealerId")+" \n");
		}
		if(Utility.testString(map.get("roNo"))) {
			sqlStr.append(" and r.RO_NO LIKE '%"+map.get("roNo")+"%' \n");
		}
		if(Utility.testString(map.get("repairType"))) {
			sqlStr.append(" and r.REPAIR_TYPE_CODE = '"+map.get("repairType")+"' \n");
		}
		if(Utility.testString(map.get("roCreateDate"))) {
			sqlStr.append(" and r.RO_CREATE_DATE >= to_date('"+map.get("roCreateDate")+"', 'yyyy-mm-dd hh24:mi:ss')\n");
		}
		if(Utility.testString(map.get("deliveryDate"))) {
			sqlStr.append(" AND r.DELIVERY_DATE <= to_date('"+map.get("deliveryDate")+" 23:59:59', 'yyyy-mm-dd hh24:mi:ss') \n");
		}
		//结算开始时间
		if(Utility.testString(map.get("balanceDateStart"))) {
			sqlStr.append(" and r.UPDATE_DATE >= to_date('"+map.get("balanceDateStart")+"', 'yyyy-mm-dd hh24:mi:ss')\n");
		}
		//结算结束时间
		if(Utility.testString(map.get("balanceDateEnd"))) {
			sqlStr.append(" AND r.UPDATE_DATE <= to_date('"+map.get("balanceDateEnd")+" 23:59:59', 'yyyy-mm-dd hh24:mi:ss') \n");
		}
		if (Utility.testString(map.get("customerName"))) {
			sqlStr.append(" and r.owner_name like '%"+map.get("customerName")+"%' \n");
		}
		if(Utility.testString(map.get("isForl"))) {
			sqlStr.append(" and r.FORL_STATUS = '"+map.get("isForl")+"' \n");
		}
		if(Utility.testString(map.get("roStatus"))) {
			sqlStr.append(" and r.RO_STATUS = '"+map.get("roStatus")+"' \n");
		}
		// 车辆VIN码
		if (Utility.testString(map.get("vin"))) {// VIN不为空
			sqlStr.append(GetVinUtil.getVins(map.get("vin").replaceAll("'",
					"\''"), "r"));
		}
		if (Utility.testString(map.get("createDateStr"))) {
			sqlStr.append(" and r.CREATE_DATE >= to_date('"+map.get("createDateStr")+"', 'yyyy-mm-dd hh24:mi:ss')\n");
		}
		if (Utility.testString(map.get("createDateEnd"))) {
			sqlStr.append(" AND r.CREATE_DATE <= to_date('"+map.get("createDateEnd")+" 23:59:59', 'yyyy-mm-dd hh24:mi:ss') \n");
		}
		sqlStr.append("  order by r.ID DESC ");
		return pageQuery(TtAsRepairOrderExtPO.class, sqlStr.toString(), null,
				pageSize, curPage);
	}

	//根据vin查询车辆信息
	public List<TmVehiclePO> getCar(String vin){
		String sql = "select * from tm_vehicle v where v.vin = '"+vin+"'" ;
		return this.select(TmVehiclePO.class, sql.toString(), null);
	}
	/**********
	 * 根据org_id查询是否大区
	 * @param ro_no
	 * @return
	 */
	public List<Map<String, Object>> viewOrgLevel(String org_id){
		  StringBuilder sql= new StringBuilder();
		  sql.append("select org.org_level from tm_org  org where org.org_id="+org_id+"");
		  System.out.println("----------------------"+sql.toString());
		  return this.pageQuery(sql.toString(), null, this.getFunName());
	}
	/**********
	 * 查询出firstPart
	 * @param claimId
	 * @return
	 */
	public List<Map<String, Object>> viewFirstPart(String claimId){
		  StringBuilder sql= new StringBuilder();
		  sql.append(" select distinct first_part,labour_id,wr_labourcode from TT_AS_WR_LABOURITEM where  id="+claimId+" and first_part is not null ");
		  System.out.println("----------------------"+sql.toString());
		  return this.pageQuery(sql.toString(), null, this.getFunName());
	}
	/**********
	 * 查询出PART_CODE
	 * @param claimId
	 * @return
	 */
	public List<Map<String, Object>> viewPartCode(Long claimId,String part_code,String wr_labour_code){
		  StringBuilder sql= new StringBuilder();
		  sql.append("select max(p.part_id) as part_id from TT_AS_WR_PARTSITEM p where p.id="+claimId+" and wr_labourcode='"+wr_labour_code+"' and  p.part_code='"+part_code+"'");
		  System.out.println("----------------------"+sql.toString());
		  return this.pageQuery(sql.toString(), null, this.getFunName());
	}
	/**********
	 * 查询出PART_CODE
	 * @param claimId
	 * @return
	 */
	public List<Map<String, Object>> viewSupplier(String partCode){
		  StringBuilder sql= new StringBuilder();
			sql.append("select sr.supplier_id from TM_PT_PART_SUP_RELATION sr where sr.order_id  in (\n" );
			sql.append("select pb.part_id from  tm_pt_part_base pb where pb.part_code='"+partCode+"')\n" );
		  System.out.println("----------------------"+sql.toString());
		  return this.pageQuery(sql.toString(), null, this.getFunName());
	}
	
	//zhumingwei add by 2011-02-25
	public List<Map<String, Object>> selectCode(){
		StringBuilder sql= new StringBuilder();
		sql.append("select distinct a.vrt_name,a.vrt_code from tm_ccc a\n" );
		System.out.println("----------------------"+sql.toString());
		return this.pageQuery(sql.toString(), null, this.getFunName());
	}
	
	//更新
	public void updateFirstCode(Long labourId,long partId){
		  StringBuilder sql= new StringBuilder();
		  sql.append("update  TT_AS_WR_LABOURITEM l set l.first_part='"+partId+"' where l.labour_id="+labourId+"");
		  System.out.println("----------------------"+sql.toString());
		  dao.update(sql.toString(), null);
	}
	/**********
	 * 根据Claim查询出重复的工时
	 * @param claimId
	 * @return
	 */
	public List<Map<String, Object>> viewRepeatedlyLabour(Long ro_id){
		  StringBuilder sql= new StringBuilder();
		  sql.append("SELECT WL.ro_id,COUNT(1),wl.wr_labourcode as code FROM tt_as_ro_labour WL WHERE wl.ro_id="+ro_id+"\n");
		  sql.append("GROUP BY WL.ro_id ,wl.wr_labourcode  HAVING COUNT(WL.Wr_Labourcode)>1");
		  System.out.println("----------------------"+sql.toString());
		  return this.pageQuery(sql.toString(), null, this.getFunName());
	}
	/**********
	 * 根据查询出重复工时中最大的明细ID
	 * @param claimId
	 * @return
	 */
	public List<Map<String, Object>> viewLabourCode(Long ro_id,String labourCode){
		  StringBuilder sql= new StringBuilder();

		  sql.append("SELECT max(id) as labour_id FROM tt_as_ro_labour l where l.ro_id="+ro_id+" and l.wr_labourcode='"+labourCode+"'");

		  System.out.println("----------------------"+sql.toString());
		  return this.pageQuery(sql.toString(), null, this.getFunName());
	}
	public void deleteLabourItem(String labourId,Long ro_id){
	String sql = "delete from tt_as_ro_labour wl where wl.ro_id="+ro_id+" and wl.id="+labourId;
		factory.delete(sql.toString(), null);
	}
	/**********
	 * 根据查询出重复工时中最大的明细ID
	 * @param claimId
	 * @return
	 */
	public List<Map<String, Object>> viewModelId(String vin ){
		  StringBuilder sql= new StringBuilder();
		  sql.append("select v.model_id from tm_vehicle v where v.vin='"+vin+"'");
		  System.out.println("----------------------"+sql.toString());
		  return this.pageQuery(sql.toString(), null, this.getFunName());
	}
	
	public List<Map<String, Object>> viewIsNewPart(String ro_id ){
		  StringBuilder sql= new StringBuilder();
		  sql.append("select count(1) as count from tm_pt_part_base pp where pp.is_new_part =1  and\n");
		  sql.append("pp.part_code in (\n" );
		  sql.append("select rp.part_no from TT_AS_RO_REPAIR_PART rp where rp.ro_id="+ro_id+")");
		  System.out.println("----------------------"+sql.toString());
		  return this.pageQuery(sql.toString(), null, this.getFunName());
	}
	public List<Map<String, Object>> viewIsNewPartString(String ro_id ){
		  StringBuilder sql= new StringBuilder();
		  sql.append("select pp.part_code as PART_NAME from tm_pt_part_base pp where pp.is_new_part =1  and\n");
		  sql.append("pp.part_code in (\n" );
		  sql.append("select rp.part_no from TT_AS_RO_REPAIR_PART rp where rp.ro_id="+ro_id+")");
		  System.out.println("----------------------"+sql.toString());
		  return this.pageQuery(sql.toString(), null, this.getFunName());
	}
	public List<Map<String, Object>> viewRoNoCount(String ro_no ){
		  StringBuilder sql= new StringBuilder();
		  sql.append("select count(1) as COUNT from tt_as_repair_order ro where ro.ro_no='"+ro_no+"'");
		  return this.pageQuery(sql.toString(), null, this.getFunName());
	}
	
	/**
	 * Iverson add with 2010-11-12
	 * 删除工单信息
	 * @param po
	 */
	public void deleteOrder(TtAsRepairOrderPO po){
		  this.delete(po);
	}
	/**
	 * Iverson add with 2010-11-12
	 * 根据工单ID查询工单信息信息
	 * @param po
	 */
	public List selectRepairOrderById(TtAsRepairOrderPO po){
		List list = new ArrayList();
		list = this.select(po);
		return list;
	}
	/**
	 * Iverson add with 2010-11-12
	 * 插入工单备份表数据
	 * @param po
	 */
	public void insertRepairOrder(TtAsRepairOrderBackupPO po){
		this.insert(po);
	}
	
	/**
	 * Iverson add with 2010-11-12
	 * 根据索赔单ID查询索赔单信息信息
	 * @param po
	 */
	public void deleteApplication(TtAsWrApplicationPO po){
		  this.delete(po);
	}
	
	/**
	 * Iverson add with 2010-11-12
	 * 插入索赔单备份表数据
	 * @param po
	 */
	public int insertApplication(long id){
		int count = 0;
		List list = new ArrayList();
		list.add(id);
		count = update("insert into tt_as_wr_application_backup select * from tt_as_wr_application wa where wa.id=?", list);
		return count;
	}
	/**
	 * Iverson add with 2010-12-15
	 * 根据索赔单ID查询索赔单和结算单的关系表信息
	 * @param po
	 */
	public void deleteTrBalanceClaim(TrBalanceClaimPO po){
		  this.delete(po);
	}
	
	public int updateApplication(long id){
		int count = 0;
		List list = new ArrayList();
		list.add(id);
		count = update("update tt_as_wr_application a set a.application_del='"+Constant.RO_APP_STATUS_03+"' where a.id=?", list);
		return count;
	}
	
	public List selectApplicationById(TtAsWrApplicationPO po){
		List list = new ArrayList();
		  StringBuilder sql= new StringBuilder();
		  sql.append("select * from   tt_as_wr_application p");
		  System.out.println("----------------------"+sql.toString());
		  list = dao.select(po);
		  return list;
	  }
	
	/********add by liuxh 20101117 增加车厂工单查询功能*********/
	  /**
     * 查询结算单使用
     * ADD 2010-10-24  只为工单查询使用
     * @param map 查询条件
     * @param pageSize 
     * @param curPage
     * @return PageResult<TtAsRepairOrderExtPO>
     */
	public PageResult<TtAsRepairOrderExtPO> queryRepairOrderNewQuery(
			Map<String, String> map, int pageSize, int curPage) {
		StringBuffer sqlStr= new StringBuffer();
		sqlStr.append("select (select count(1) from tt_as_wr_application where ro_no=r.ro_no) as in_claim,r.*,d.dealer_shortname\n" );
        sqlStr.append(" from tt_as_repair_order r\n" );
		sqlStr.append("left outer join tm_dealer d on r.dealer_code=d.dealer_code \n");
		sqlStr.append(" where 1=1");
		if(Utility.testString(map.get("dealerId"))) {
			sqlStr.append(" and d.DEALER_ID="+map.get("dealerId")+" \n");
		}
		if(Utility.testString(map.get("roNo"))) {
			sqlStr.append(" and r.RO_NO LIKE '%"+map.get("roNo")+"%' \n");
		}
		if(Utility.testString(map.get("repairType"))) {
			sqlStr.append(" and r.REPAIR_TYPE_CODE = '"+map.get("repairType")+"' \n");
		}
		if(Utility.testString(map.get("roCreateDate"))) {
			sqlStr.append(" and r.RO_CREATE_DATE >= to_date('"+map.get("roCreateDate")+"', 'yyyy-mm-dd hh24:mi:ss')\n");
		}
		if(Utility.testString(map.get("deliveryDate"))) {
			sqlStr.append(" AND r.DELIVERY_DATE <= to_date('"+map.get("deliveryDate")+" 23:59:59', 'yyyy-mm-dd hh24:mi:ss') \n");
		}
		//结算开始时间
		if(Utility.testString(map.get("balanceDateStart"))) {
			sqlStr.append(" and r.UPDATE_DATE >= to_date('"+map.get("balanceDateStart")+"', 'yyyy-mm-dd hh24:mi:ss')\n");
		}
		//结算结束时间
		if(Utility.testString(map.get("balanceDateEnd"))) {
			sqlStr.append(" AND r.UPDATE_DATE <= to_date('"+map.get("balanceDateEnd")+" 23:59:59', 'yyyy-mm-dd hh24:mi:ss') \n");
		}
		if (Utility.testString(map.get("customerName"))) {
			sqlStr.append(" and r.owner_name like '%"+map.get("customerName")+"%' \n");
		}
		if(Utility.testString(map.get("isForl"))) {
			sqlStr.append(" and r.FORL_STATUS = '"+map.get("isForl")+"' \n");
		}
		if(Utility.testString(map.get("roStatus"))) {
			sqlStr.append(" and r.RO_STATUS = '"+map.get("roStatus")+"' \n");
		}
		// 车辆VIN码
		if (Utility.testString(map.get("vin"))) {// VIN不为空
			sqlStr.append(GetVinUtil.getVins(map.get("vin").replaceAll("'",
					"\''"), "r"));
		}
		if (Utility.testString(map.get("createDateStr"))) {
			sqlStr.append(" and r.CREATE_DATE >= to_date('"+map.get("createDateStr")+"', 'yyyy-mm-dd hh24:mi:ss')\n");
		}
		if (Utility.testString(map.get("createDateEnd"))) {
			sqlStr.append(" AND r.CREATE_DATE <= to_date('"+map.get("createDateEnd")+" 23:59:59', 'yyyy-mm-dd hh24:mi:ss') \n");
		}
		if(!(map.get("dealerIdCon").toString().equals(""))){
			sqlStr.append(" and r.dealer_code in(select dea.dealer_code from tm_dealer dea where dea.dealer_id in("+map.get("dealerIdCon").toString()+"))");
		}
		sqlStr.append("  order by r.ID DESC ");
		return this.pageQuery(sqlStr.toString(), null, this.getFunName(), pageSize, curPage);
//		return pageQuery(TtAsRepairOrderExtPO.class, sqlStr.toString(), null,
//				pageSize, curPage);
	}
	/********add by liuxh 20101117 增加车厂工单查询功能*********/
	public PageResult<Map> vehicleCusInfoQuery(
			Map<String, String> map, int pageSize, int curPage) {
		StringBuffer sb=new StringBuffer("\n");
		sb.append("   SELECT A.VEHICLE_ID,A.VIN,A.ENGINE_NO,A.LICENSE_NO, " );
		sb.append("   g.group_code  AS MODEL_NAME, " );
		sb.append("   (SELECT C.CTM_NAME FROM TT_DEALER_ACTUAL_SALES B,TT_CUSTOMER C WHERE B.CTM_ID=C.CTM_ID AND B.VEHICLE_ID=A.VEHICLE_ID) AS CUS_NAME, " );
		sb.append("   (SELECT SALES_DATE FROM TT_DEALER_ACTUAL_SALES B WHERE B.VEHICLE_ID=A.VEHICLE_ID AND IS_RETURN=?) AS SALES_DATE, " );
		sb.append("   A.PRODUCT_DATE,A.YIELDLY,A.MILEAGE,A.FREE_TIMES,A.LIFE_CYCLE    " );
		sb.append("   FROM TM_VEHICLE A,tm_vhcl_material_group g " );
		sb.append("WHERE a.package_id = g.group_id(+) and A.YIELDLY IN("+(String)map.get("yieldlyUser")+") ");
		sb.append("AND A.LIFE_CYCLE IN(?,?,?,?,?) ");
		List par=new ArrayList();
		par.add(Constant.IF_TYPE_NO);
		par.add(Constant.VEHICLE_LIFE_01);
		par.add(Constant.VEHICLE_LIFE_02);
		par.add(Constant.VEHICLE_LIFE_03);
		par.add(Constant.VEHICLE_LIFE_04);
		par.add(Constant.VEHICLE_LIFE_05);
		String vin=map.get("vin").toString();
		String carStatus=map.get("carStatus").toString();
		String yieldly=map.get("yieldly").toString();
		String carNo=map.get("carNo").toString();
		String cusName=map.get("cusName").toString();
		if(!vin.equals("")){
			sb.append("AND A.VIN LIKE ? ");
			par.add("%"+vin+"%");
		}
		if(!carStatus.equals("")){
			if(carStatus.equals(Constant.CAR_STATUS_SALES)){//未售
				sb.append("AND A.LIFE_CYCLE IN(?,?,?,?) ");
				par.add(Constant.VEHICLE_LIFE_01);
				par.add(Constant.VEHICLE_LIFE_02);
				par.add(Constant.VEHICLE_LIFE_03);
				par.add(Constant.VEHICLE_LIFE_05);
			}
			if(carStatus.equals(Constant.CAR_STATUS_SALES_NO)){//已售
				sb.append("AND A.LIFE_CYCLE=? ");
				par.add(Constant.VEHICLE_LIFE_04);
			}
		}
		if(!"".equals(yieldly)){
			sb.append("AND A.YIELDLY=? ");
			par.add(yieldly);
		}
		if(!carNo.equals("")){
			sb.append("AND A.LICENSE_NO LIKE ? ");
			par.add("%"+carNo+"%");
		}
		if(!cusName.equals("")){
			sb.append("AND EXISTS (SELECT C.CTM_NAME FROM TT_DEALER_ACTUAL_SALES B,TT_CUSTOMER C WHERE B.CTM_ID=C.CTM_ID AND B.VEHICLE_ID=A.VEHICLE_ID AND C.CTM_NAME LIKE ? )");
			par.add("%"+cusName+"%");
		}
		
		return this.pageQuery(sb.toString(), par, this.getFunName(), pageSize, curPage);
	}
	public List getVehcleCusDetial(long velId){
		    POFactory factory = POFactoryBuilder.getInstance();
			StringBuffer sb=new StringBuffer();
		
		        sb.append("   SELECT A.VEHICLE_ID,A.VIN,A.ENGINE_NO,A.LICENSE_NO, ");
		        sb.append("   (SELECT B.GROUP_NAME FROM TM_VHCL_MATERIAL_GROUP B WHERE B.GROUP_ID=A.SERIES_ID) AS CX_NAME, " );
		        sb.append("   (SELECT B.GROUP_NAME FROM TM_VHCL_MATERIAL_GROUP B WHERE B.GROUP_ID=a.Package_Id) AS MODEL_NAME, " );
		        sb.append("   (SELECT C.CTM_NAME FROM TT_DEALER_ACTUAL_SALES B,TT_CUSTOMER C WHERE B.CTM_ID=C.CTM_ID AND B.VEHICLE_ID=A.VEHICLE_ID) AS CUS_NAME, " );
		        sb.append("   (SELECT TO_CHAR(B.SALES_DATE,'YYYY-MM-DD') FROM TT_DEALER_ACTUAL_SALES B WHERE B.VEHICLE_ID=A.VEHICLE_ID) AS SALES_DATE, ");
		        sb.append("   TO_CHAR(A.PRODUCT_DATE,'YYYY-MM-DD') PRODUCT_DATE,A.YIELDLY,A.MILEAGE,A.FREE_TIMES,A.LIFE_CYCLE, " );
		        sb.append("   (SELECT C.PROVINCE FROM TT_DEALER_ACTUAL_SALES B,TT_CUSTOMER C WHERE B.CTM_ID=C.CTM_ID AND B.VEHICLE_ID=A.VEHICLE_ID) AS PROVINCE, ");
		        sb.append("   (SELECT C.CITY FROM TT_DEALER_ACTUAL_SALES B,TT_CUSTOMER C WHERE B.CTM_ID=C.CTM_ID AND B.VEHICLE_ID=A.VEHICLE_ID) AS CITY, " );
		        sb.append("   (SELECT C.ADDRESS FROM TT_DEALER_ACTUAL_SALES B,TT_CUSTOMER C WHERE B.CTM_ID=C.CTM_ID AND B.VEHICLE_ID=A.VEHICLE_ID) AS ADDRESS, " );
		        sb.append("   (SELECT B.GAME_CODE FROM TT_AS_WR_GAME B WHERE A.CLAIM_TACTICS_ID=B.ID) AS GAME_CODE ");
		        sb.append( "   FROM TM_VEHICLE A ");
		        sb.append("  WHERE A.VEHICLE_ID=? ");
		        List par=new ArrayList();
		        par.add(velId);
		       return this.pageQuery(sb.toString(), par, this.getFunName());	
	}
	/********add by liuxh 20101117 增加车厂工单查询功能*********/
	//获得外出维修信息
	public List getOutInfo(String id){
		StringBuffer sql = new StringBuffer("\n");
		sql.append("select *\n");
		sql.append("  from tt_as_wr_outrepair\n");  
		sql.append(" where ro_no = (select ro_no from tt_as_wr_application where id ="+id+")\n");
		return this.select(TtAsWrOutrepairPO.class, sql.toString(), null);
	}
	//获取工单的DEALER_ID
		public List<Map<String, Object>> getRepairOrder(String roId){
			StringBuffer sql = new StringBuffer("\n");
		   sql.append(" select r.dealer_id ID  from tt_as_repair_order r where r.id="+roId+" \n");
		   return this.pageQuery(sql.toString(), null, this.getFunName());
	}
	//获取加价率
	public List<Map<String, Object>> selectPrice(){
		TmBusinessParaPO po = new TmBusinessParaPO();
		  StringBuilder sql= new StringBuilder();
		  sql.append("select * from tm_business_para p where p.type_code="+Constant.SALE_PRICE+" ");
		  return this.pageQuery(sql.toString(), null, this.getFunName());
	  }
	  	
	/********Iverson add by 2010-12-08 判断vin码在库中是否存在*********/
	public int getCount(String vin){
		String sql = "select count(*) as count from tm_vehicle f where f.vin = '"+vin+"'";
		List<Map<String, Object>> list = this.pageQuery(sql, null, this.getFunName());
		return ((BigDecimal)list.get(0).get("COUNT")).intValue();
	}
	/********Iverson add by 2010-12-08 判断vin码在库中是否存在*********/
	
	/********yx add by 2010-12-17 查询经销提报车辆PIN申请*********/
	public PageResult<Map<String,Object>> getVehclePinSubmitRequest(String companyName,String vin,
			String pinCreateDate,String pinEndDate,String status,String pinCode,String dealerCode,Integer curPage){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT A.PIN_ID,\n");
		sql.append("       A.DEALER_ID,\n");
		sql.append("       A.PIN_CODE,\n");
		sql.append("       A.MAKE_DATE,\n");
		sql.append("       A.VIN,\n");
		sql.append("       A.REMARK,\n");
		sql.append("       A.BACK_REMARK,\n");
		sql.append("       A.STATUS,\n");
		sql.append("       A.CREATE_BY,\n");
		sql.append("       A.CREATE_DATE,\n");
		sql.append("       C.COMPANY_CODE,\n");
		sql.append("       C.COMPANY_SHORTNAME,\n");
		sql.append("       E.DEALER_CODE,\n");
		sql.append("       E.DEALER_SHORTNAME,\n");
		sql.append("       (SELECT D.NAME FROM TC_USER D WHERE D.USER_ID=A.CREATE_BY) AS USER_NAME\n");
		sql.append("  FROM TM_VEHICLE_PIN_REQUEST A,TM_COMPANY C,TM_DEALER E\n");
		sql.append(" WHERE E.COMPANY_ID = C.COMPANY_ID\n");
		sql.append(" AND E.DEALER_ID=A.DEALER_ID\n");
		sql.append(" AND E.DEALER_TYPE='"+Constant.DEALER_TYPE_DWR+"'\n");
		
		if(Utility.testString(companyName)){
			sql.append(" AND C.COMPANY_NAME LIKE '%"+companyName+"%'\n");
		}
		if(Utility.testString(vin)){
			sql.append(" AND A.VIN LIKE '%"+vin+"%'\n");		
		}
		if(Utility.testString(pinCreateDate)){
			sql.append(" and A.CREATE_DATE >= to_date('"+pinCreateDate+"', 'yyyy-mm-dd hh24:mi:ss')\n");
		}
		if(Utility.testString(pinEndDate)){
			sql.append(" AND A.CREATE_DATE <= to_date('"+pinEndDate+" 23:59:59', 'yyyy-mm-dd hh24:mi:ss') \n");
		}
		if(Utility.testString(pinCode)){
			sql.append(" AND A.PIN_CODE LIKE '%"+pinCode+"%'\n");
		}
		if(Utility.testString(status)){
			sql.append(" AND A.STATUS="+status+"\n");		
		}
		if(Utility.testString(dealerCode)){
			sql.append(" AND E.DEALER_CODE='"+dealerCode+"'\n");		
		}
		sql.append(" ORDER BY A.CREATE_DATE DESC \n");
		PageResult<Map<String, Object>> ps = pageQuery(sql.toString(), null, getFunName(), Constant.PAGE_SIZE, curPage);
		return ps;
	}
	/********yx add by 2010-12-17查询经销提报车辆PIN申请*********/
	/********yx add by 2010-12-17 查询经销提报车辆PIN申请明细*********/
	public Map<String,Object> getVehclePinDetail(String pinId){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT A.PIN_ID,\n");
		sql.append("       A.DEALER_ID,\n");
		sql.append("       A.PIN_CODE,\n");
		sql.append("       A.MAKE_DATE,\n");
		sql.append("       A.VIN,\n");
		sql.append("       A.REMARK,\n");
		sql.append("       A.BACK_REMARK,\n");
		sql.append("       A.STATUS,\n");
		sql.append("       A.CREATE_BY,\n");
		sql.append("       A.CREATE_DATE,\n");
		sql.append("       C.DEALER_CODE,\n");
		sql.append("       C.DEALER_SHORTNAME,\n");
		sql.append("       D.NAME\n");
		sql.append("  FROM TM_VEHICLE_PIN_REQUEST A,TM_DEALER C,TC_USER D\n");
		sql.append(" WHERE A.PIN_ID="+pinId+"\n");
		sql.append(" AND D.USER_ID=A.CREATE_BY\n");
		sql.append(" AND A.DEALER_ID=C.DEALER_ID\n");
		sql.append(" AND C.DEALER_TYPE='"+Constant.DEALER_TYPE_DWR+"'\n");
//		sql.append(" AND A.CREATE_BY="+dealerId+"\n");
		Map<String, Object> ps = (Map<String, Object>) pageQuery(sql.toString(), null, this.getFunName()).get(0); 
		return ps;
	}
	/********yx add by 2010-12-17查询经销提报车辆PIN申请明细*********/
	/********yx add by 2010-12-17 查询经销提报车辆PIN申请明细*********/
	public int replyVehclePin(TmVehiclePinRequestPO pinReq,TmVehiclePinRequestPO pinReqValue){
		return this.update(pinReq, pinReqValue);
	}
	/********yx add by 2010-12-17查询经销提报车辆PIN申请明细*********/
	
	/**
	 * 
	 * @Title: queryPartCode
	 * @Description: TODO(根据三包规则查询配件)
	 * @param
	 * @param user
	 * @param
	 * @param con
	 * @param
	 * @param pageSize
	 * @param
	 * @param curPage
	 * @param
	 * @return 设定文件
	 * @return PageResult<TmPtPartBaseExtPO> 返回类型
	 * @throws
	 */
	public Map<String,Object> queryPartCodeByVin(AclUserBean user,
			Map<String, String> map) {
		String vin = map.get("vin").toString();
		String partCode = map.get("partCode")==null?"":map.get("partCode").toString();
//		String partName = map.get("partName")==null?"":map.get("partName").toString();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT D.PART_CODE,\n");
		sql.append("       D.PART_NAME,\n");
		sql.append("       D.ID AS LISTID,\n");
		sql.append("       D.CLAIM_MONTH,\n");
		sql.append("       D.CLAIM_MELIEAGE,\n");
		sql.append("       B.GAME_CODE,\n");
		sql.append("       B.GAME_NAME,\n");
		sql.append("       B.ID AS GAMEID,\n");
		sql.append("       C.RULE_CODE,\n");
		sql.append("       C.RULE_NAME,\n");
		sql.append("       C.ID AS RULEID\n");
		sql.append("  FROM TM_VEHICLE         A,\n");
		sql.append("       TT_AS_WR_GAME      B,\n");
		sql.append("       TT_AS_WR_RULE      C,\n");
		sql.append("       TT_AS_WR_RULE_LIST D\n");
		sql.append(" WHERE A.CLAIM_TACTICS_ID = B.ID\n");
		sql.append("   AND B.RULE_ID = C.ID\n");
		sql.append("   AND C.ID = D.RULE_ID\n");
		sql.append("   AND A.VIN = '"+vin+"'\n");
		sql.append(" AND D.PART_CODE = '"+partCode+"'\n");
		//sql.append("   AND A.DEALER_ID="+user.getDealerId()+"\n");
//		if(Utility.testString(partCode)){
//			sql.append(" AND D.PART_CODE LIKE '%"+partCode+"%'\n");
//		}
//		if(Utility.testString(partName)){
//			sql.append(" AND D.PART_NAME LIKE '%"+partName+"%'\n");
//		}

		Map<String,Object> ps = this.pageQueryMap(sql.toString(), null, getFunName());
		return ps;
	}
	/**
	 * 
	 * @Title: queryPartCode
	 * @Description: TODO(根据三包规则查询配件)
	 * @param查询通用三包规则配件
	 * @throws
	 */
	public Map<String,Object> queryUsualRulePartCode(String partCode) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT D.PART_CODE,\n");
		sql.append("       D.PART_NAME,\n");
		sql.append("       D.ID AS LISTID,\n");
		sql.append("       D.CLAIM_MONTH,\n");
		sql.append("       D.CLAIM_MELIEAGE,\n");
		sql.append("       B.GAME_CODE,\n");
		sql.append("       B.GAME_NAME,\n");
		sql.append("       B.ID AS GAMEID,\n");
		sql.append("       C.RULE_CODE,\n");
		sql.append("       C.RULE_NAME,\n");
		sql.append("       C.ID AS RULEID\n");
		sql.append("  FROM \n");
		sql.append("       TT_AS_WR_GAME      B,\n");
		sql.append("       TT_AS_WR_RULE      C,\n");
		sql.append("       TT_AS_WR_RULE_LIST D\n");
		sql.append(" WHERE C.RULE_CODE='20060101'\n");
		sql.append("   AND B.RULE_ID = C.ID\n");
		sql.append("   AND C.ID = D.RULE_ID\n");
		sql.append(" AND D.PART_CODE = '"+partCode+"'\n");
		//sql.append("   AND A.DEALER_ID="+user.getDealerId()+"\n");
//		if(Utility.testString(partCode)){
//			sql.append(" AND D.PART_CODE LIKE '%"+partCode+"%'\n");
//		}
//		if(Utility.testString(partName)){
//			sql.append(" AND D.PART_NAME LIKE '%"+partName+"%'\n");
//		}

		Map<String,Object> ps = this.pageQueryMap(sql.toString(), null, getFunName());
		return ps;
	}
	/**
	 * 
	 * @Title: queryPartCode
	 * @Description: TODO(查询配件)
	 * 通过配件CODE，配件名称查询配件代码（三包信息变更申请查询）
	 * @throws
	 */
	public PageResult<TmPtPartBaseExtPO> queryPartCodeForRuleChange(String partCode,String partName, int pageSize, int curPage) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT DISTINCT(A.PART_CODE) AS PART_CODE,A.PART_ID,A.PART_NAME FROM TM_PT_PART_BASE A WHERE 1=1");

		if (Utility.testString(partCode)) {
			sql.append(" AND A.PART_CODE LIKE '%" + partCode
					+ "%' ");
		}
		if (Utility.testString(partName)) {
			sql.append(" AND A.PART_NAME LIKE '%" + partName
					+ "%' ");
		}
		//sqlStr.append(" GROUP ");
		// sqlStr.append(con);
		PageResult<TmPtPartBaseExtPO> ps = pageQuery(TmPtPartBaseExtPO.class,
				sql.toString(), null, pageSize, curPage);
		return ps;
	}
	
	public List<Map<String,String>> getRepairOrderPartCheck(String roNo) {
		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT B.PART_TYPE_ID,A.PART_NO,A.PART_NAME\n");
		sql.append("  FROM tt_as_ro_repair_part A, Tm_Pt_Part_Base B\n");
		sql.append(" WHERE A.RO_ID = '"+roNo+"'\n");
		sql.append("   AND B.PART_CODE=A.PART_NO");

		List<Map<String,String>> ps = this.pageQuery(sql.toString(), null, getFunName());
		return ps;
	}
	  /*******
	   *   经销商进入DQV规则时删除其他自动审核规则产生的审核记录
	   * 
	   * @param id
	   */
	   public  void delAppStatus(String id){
	    	StringBuffer sql = new StringBuffer();
	    	sql.append(" delete from Tt_As_Wr_Appauthitem where id="+id);
	    	this.delete(sql.toString(), null);
	    }
}
	







